Getting top x number of days per Unique ID

[cfvito]

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 ID	Name	Date	# of Days	Tile ID
5527	Lucas	1/5/2022	5	1
5527	Lucas	2/11/2022	5	2
5527	Lucas	3/3/2022	5	3
5527	Lucas	7/15/2022	5	4
5527	Lucas	8/10/2022	5	5
5527	Lucas	11/8/2022	5	6
5525	Mike	3/20/2022	3	1
5525	Mike	4/25/2022	3	2
5525	Mike	5/1/2022	3	3
5525	Mike	12/31/2022	3	4
5526	Noah	4/1/2022	2	1
5526	Noah	8/9/2022	2	2
5526	Noah	9/12/2022	2	3

[JimmyV]

• Add in Summarize tool
  • Group By Record ID
  • Max on # of Days

 

[DataNath · Accepted solution]

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

[cfvito]

Thank you

[cfugat]

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.

[cfvito]

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.