Group and get latest classification of a week

[Anku]

Hello everyone, 

please refer to snapshot , the input table has dates and classification column along with other columns. I want to create this shown output where latest classification is selected for each key as per week

note: week starts on Mon and ends on Sun. And I have highlighted dates falling in same week.

I want to do it at script level only.

 

[Pierrick]

Hello,

To do this you can use the FirstSortedValue function. It allows you to take the first result according to an aggregation and a sorting order.

Here's an example script to adapt to your needs

//Load data and create week field / new Key
INPUT:
Load
	*,
    WeekName(Date) as [Week], //Create field Week
    [Key]&'|'&WeekName(Date) as [Key_Week]; //Create a key to join the new result table
Load
	[Key],
    timestamp(timestamp#([Date],'M/D/YYYY')) as [Date],
    [Class];
Load * inline [
	Key, Date, Class
	1, 3/10/2024, A
	2, 3/11/2024, D
	3, 3/12/2024, B
	4, 3/13/2024, C
	5, 3/14/2024, C
	1, 3/11/2024, B
	2, 3/12/2024, D
	3, 3/13/2024, A
	4, 3/14/2024, C
	5, 3/15/2024, A
	1, 3/12/2024, C
	2, 3/13/2024, D
	3, 3/14/2024, C
	4, 3/15/2024, C
	5, 3/16/2024, A
	1, 3/13/2024, D
	2, 3/14/2024, A
	4, 3/16/2024, C
	5, 3/17/2024, A
	1, 3/18/2024, A
	2, 3/19/2024, B
	3, 3/20/2024, C
];

//New table
OUTPUT:
Load
	[Key_Week], 
    FirstSortedValue([Class],-[Date]) as [Last Class] //-[Date] for the latest date
Resident INPUT
group by [Key_Week];

[Anku]

Hi, thanks for your response. I can still see more than 1 class for a key.

I am looking for a solution where I can have latest class value for its respective key falling within a week.