topic Re: Group and get latest classification of a week in App Development

Group and get latest classification of a week

Anku — Fri, 26 Apr 2024 13:11:11 GMT

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.

Re: Group and get latest classification of a week

Pierrick — Fri, 26 Apr 2024 14:55:23 GMT

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];

Re: Group and get latest classification of a week

Anku — Mon, 29 Apr 2024 08:58:09 GMT

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.