Is it Possible to get a sequential count based on 2 columns?

Options
Karlo
Karlo
in Frequently Asked Questions

For example: When Column A and B are concatenated it will create unique data. I need to be able to count sequentially the unique data only that is concatenated within column A.

A

B

Concat A + B

Count Unique

12345

Blue

12345Blue

1

12345

Blue

12345Blue

12345

Red

12345Red

2

45678

Green

45678Green

1

91011

Blue

91011Blue

1

91011

Red

91011Red

2

91011

Green

91011Green

3

91011

Red

91011Red

91011

Red

91011Red

Answers

  • Josh Boxer
    Josh Boxer Administrator
    Options

    Hi Karlo, please can you explain further (or maybe start a new discussion to replace this).

    1. When Column A and B are concatenated it will create unique data. - not in your example, rows 1&2 and 6,8,9 are not unique
    2. count sequentially the unique data only that is concatenated within column A. - not sure I understand this

  • Karlo
    Karlo
    Options

    Hi,

    To clarify column A can have multiple identical numbers. Column B can have multiple identical colours. The 2 columns can be concatenated and harmonized to identify the unique combinations.

    For your second query as per column "Count Unique" It will count sequentially the unique combinations. For example with 12345 there are 2 unique combination so the sequential count is 1,2. For 91011 there are 3 unique combinations so the count is 1,2,3.

    Thanks,

    Karlo

  • Josh Boxer
    Josh Boxer Administrator
    Options

    Thanks for clarifying it is a little clearer now. Thinking of a solution reminded me of this which might be helpful:

    https://community.experianaperture.io/discussion/837/how-to-determine-nth-occurence-in-a-column

    Was getting somewhere using many different combinations of Group steps and Lookups plus a Transform using Get cell Function, but was becoming quite complicated, so wondering what is the approximate volume of data (the added complexity might make it quite slow).

    A single Group step can very efficiently List all the values in B for each A with distinct selected to ignore any duplicates. Wondering if this would work or if it has to be in this exact format?

  • Karlo
    Karlo
    Options

    Hi,

    Yes, I can make this work. Thank you very much.

    Karlo

Categories