topic Re: Load data with the max version in App Development

Load data with the max version

Ankhi — Tue, 24 Aug 2021 13:06:33 GMT

I have a data set like below. The last column i.e the Want To Return Col when set to Y are the rows that I would like to return . I need to group by LID, Account, Period and take the max(Version). But I would like to have the Amount as well.

If I do a group by with Amount, due to different Amounts the group by LID, Account, Period, Amount with max(Version)

is pulling multiple rows instead only the max (Version).

Please help.

SHeaderID	LID	Account	Period	Amount	Version	Want to Return
60	3746	AMRT	Jun-21	100.13	74	Y
62	3746	INT	Jun-21	3123	74	Y
64	3746	ROU	Jun-21	324243.71	74	Y
18	3746	AMRT	Jun-21	9768	4
19	3746	CASH	Jun-21	0	4
20	3746	INT	Jun-21	3245	4
22	3746	ROU	Jun-21	23456	4
22	3746	ROU	Jun-21	3059	4
63	3746	L<1	Jun-21	4694	74	Y
21	3746	L<1	Jun-21	461	4
21	3746	L<1	Jun-21	451	4
63	3746	L>1	Jun-21	2772	74	Y
21	3746	L>1	Jun-21	2600	4
21	3746	L>1	Jun-21	2600	4

Thanks in advance.

Regards

Ankhi

Re: Load data with the max version

Kushal_Chawda — Tue, 24 Aug 2021 15:18:12 GMT

@Ankhi try below

Data: LOAD SHeaderID, LID, Account, Period, Amount, Version FROM Table; Left Join(Data) LOAD LID, Account, Period, max(Version) as Version, FirstSortedValue(Amount,-Version) as Amount, 'Y' as Flag Resident Data where Amount>0 Group by LID, Account, Period;

Re: Load data with the max version

Kushal_Chawda — Tue, 24 Aug 2021 20:16:28 GMT

@Ankhi If you just want to load the data with max version then simply try below

Data: LOAD LID, Account, Period, max(Version) as Version, FirstSortedValue(Amount,-Version) as Amount FROM Table where Amount>0 Group by LID, Account, Period;

Re: Load data with the max version

Ankhi — Wed, 25 Aug 2021 10:42:08 GMT

Thanks a lot Kushal. It is working.

Re: Load data with the max version

Ankhi — Wed, 25 Aug 2021 10:52:15 GMT

Hi Kushal,

I have one more question, if the max version number has been repeated in the data with different amount, then how will this work?

Is it going to choose random any row with the max version?

What if I want to do a sum of all the amounts with the having the max version number. The group by clause remains the same.

In the front end I am using SUM(Amount) , but for cases where there are multiple lines with same max version number , it is not summing up the amount.

Thanks again.

Regards

Ankhi

Re: Load data with the max version

Kushal_Chawda — Wed, 25 Aug 2021 14:54:49 GMT

@Ankhi do like below

Data: LOAD SHeaderID, LID, Account, Period, Amount, Version FROM Table; Left Join(Data) LOAD LID, Account, Period, max(Version) as Version, '1' as Flag Resident Data Group by LID, Account, Period; Final: LOAD LID, Account, Period, Version, sum(Amount) as Amount resident Data where Flag=1 grup by LID, Account, Period, Version; drop table Data