Getting the max record by date or field

[tvisha]

I need to pick the latest record. It is based on date or a field name. How can I pull the same.

Sample data

AAccountName	LastUpdatedDate	Version
Test1	17/10/2022 15:42	4
Test1	19/10/2022 09:39	1
Test1	19/10/2022 10:33	1
Test1	19/10/2022 10:33	2
Test2	19/10/2022 11:08	1
Test2	19/10/2022 11:10	2
Test2	19/10/2022 11:29	3
Test2	19/10/2022 11:30	4
Test3	14/10/2022 17:50	3
Test3	14/10/2022 18:30	1
Test3	17/10/2022 12:56	1
Test3	17/10/2022 12:56	2
Test3	17/10/2022 12:56	3
Test3	17/10/2022 13:10	1
Test3	17/10/2022 13:12	2
Test3	17/10/2022 13:14	3
Test3	17/10/2022 13:14	4
Test3	17/10/2022 13:15	5
Test3	17/10/2022 13:15	6
Test4	19/10/2022 10:05	1
Test4	19/10/2022 10:05	2
Output
AAccountName	LastUpdatedDate	Version
Test1	17/10/2022 15:42	4
Test2	19/10/2022 11:30	4
Test3	17/10/2022 12:56	3
Test4	19/10/2022 10:05	2

[lukas_]

Hello, 

Where do you want to do the calculation? In the script or on the visualization side?

[ckarras22]

Hello,

In the output for Test3 why did you peek 

17/10/2022 12:56

3

and not

17/10/2022 13:15

6

?

[tvisha]

yes mistake, it should be 6 for Test 3

[tvisha]

Visualization side, if not possible script should be good, but if I do script, If I have to show an aggregate or total of all, do I have to create another app. 

[lukas_ · Accepted solution]

Hello,

No, it is not necessary to make another application. I don't know your current modeling but you can add a table with just the aggregated versions linked to your other table by the AAccountName field.

I find it much easier to do this on the script side and more efficient for the user. Here is an example script to achieve this.

NoConcatenate
Table1:
load 
	AAccountName,
    LastUpdatedDate,	
    Version
resident Table;

//Group by to get the last version
Table_aggr:
load 
	AAccountName,
	max(Version)		as Version
resident Table1
group by AAccountName;

//Join to add the date
left join (Table_aggr)
load 
	AAccountName,		//Join on this field
    Version,			//Join on this field
    LastUpdatedDate
resident Table1;

// Last group by to take the last date if there are mutliple dates for the same version
NoConcatenate
Last_Table:
load 
	AAccountName,
    max(LastUpdatedDate)	as LastUpdatedDate,
    max(Version)			as Version
resident Table_aggr
group by AAccountName;

drop table Table1, Table_aggr;

[tvisha]

Thank You that works