Load data with the max version

[Ankhi]

Hi 

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

[Kushal_Chawda]

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

[Kushal_Chawda · Accepted solution]

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

[Ankhi]

Thanks a lot Kushal. It is working.

[Ankhi]

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

[Kushal_Chawda]

@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