Adding Ranking column to a dataset

[HA]

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

[gc · Accepted solution]

I think this workflow and batch macro should do it. Check it out and let me know.

Gene

[SeanAdams]

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)

• IF you sort the data by Key and the field you're ranking on.
• Then use a tile tool - tile tool, if used properly adds a unique ID in order on a group.  HAve a look at the tool mastery on this by @MattD (https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Tile/tac-p/55615/highlight/true)
• Now you have the data withan incrementing row ID for everything with AAA; everything with BBB, etc
• Then a filter tool to filter out anythign with a Tile_Seq_Num >1

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

[SeanAdams]

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

[vishwa_0308]

[HA]

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  

[Rich_Stebles]

Thank you vishwa_0308, this solution worked well for me

[redruby]

Thank you @SeanAdams , 

This helped me.