Hi,
I need to create a functionality similar to SQL RANK and only pick records with RANK=1
Following table is just an example of what I'm trying to do. In key field I have "AAA" repeated three times. Starting from the first record in the data set (order matters ) I need to assign rank 1,2,3,etc to each record.
The goal is to decide which records to keep and which ones to ignore when there are duplicate keys (the first incident is the valid record all others should be ignored)
Key | Rank |
AAA | 1 |
BBB | 1 |
CC | 1 |
AAA | 2 |
AAA | 3 |
BBB | 2 |
DDD | 1 |
EEE | 1 |
After assigning RANK to each record then I will pick only those records with RANK=1
Key | Rank |
AAA | 1 |
BBB | 1 |
CC | 1 |
DDD | 1 |
EEE | 1 |
Gelöst! Gehe zu Lösung.
Depending on your exact need, there may be another way to do this (but it's worth working through @gc 's solution - macros are probably the most powerful part of Alteryx)
And you're done.
Let me know if this or @gc 's gets you to a solution, or if not, then feel free to reply and we can work this through in a mockup.
cheers
Sean
Hey @HA,
Did you manage to get a solution that worked from the 2 contributions that worked? If not, feel free to reply and we can work this through together.
Have a good Tuesday
Sean
Thank you. This is a log file and the order of records is important so although other suggestions are interesting I cannot user sorting tool because it will reorder the records.
I think just the grouping will work assuming Alteryx has a deterministic behavior when grouping records and always picks the first record in the group which basically what I'm looking for here
Thank you vishwa_0308, this solution worked well for me