Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Getting top x number of days per Unique ID

cfvito
5 - Atom

Hi everyone. Would like to ask for suggestions on how to get output where in per Unique Record ID, I will grab just the top x dates. I already created a workflow where I sorted everything and got Tile ID. SO it will only display the number of rows based on days declared. Hope I explained it right. Appreciate your help.

x=Days (in table)

Record IDNameDate# of DaysTile ID
5527Lucas1/5/202251
5527Lucas2/11/202252
5527Lucas3/3/202253
5527Lucas7/15/202254
5527Lucas8/10/202255
5527Lucas11/8/202256
5525Mike3/20/202231
5525Mike4/25/202232
5525Mike5/1/202233
5525Mike12/31/202234
5526Noah4/1/202221
5526Noah8/9/202222
5526Noah9/12/202223
5 REPLIES 5
JimmyV
6 - Meteoroid
  • Add in Summarize tool
    • Group By Record ID
    • Max on # of Days

Solution.PNG

DataNath
16 - Nebula

Hey @cfvito, is this what you're after? The following:

 

  • Creates a true date column
  • Orders the dates from latest (most recent) backwards - I assumed this is what you meant from 'top dates'
  • Assigns a rank to all dates within each Unique ID, to be compared against x
  • Removes all ranks beyond the x (# of days)
  • Tidies the output back up to your original schema

 

DataNath_0-1655152556718.png

 

cfvito
5 - Atom

Thank you

cfugat
5 - Atom

I am a little confused by the question. Are you asking to get the max number of days per each record ID? that is answered already. BUT I read that as you were looking to find the most recent x dates per Record ID. To do this, I used the sort tool to sort by date then used the sample tool to take the First N Rows and Group by Record ID.

 

cfugat_0-1655153342269.png

 

cfvito
5 - Atom

I need to get the top number of dates based on the Days field. Thank you. I was able to get my desired output. Thank you everyone for your help.

Labels