Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Hello,
Where do you want to do the calculation? In the script or on the visualization side?
Hello,
In the output for Test3 why did you peek
17/10/2022 12:56 | 3 |
and not
17/10/2022 13:15 | 6 |
?
yes mistake, it should be 6 for Test 3
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.
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;
Thank You that works