Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

Load data with the max version

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.

SHeaderIDLIDAccountPeriodAmountVersionWant to Return 
603746AMRTJun-21100.1374Y
623746INTJun-21312374Y
643746ROUJun-21324243.7174Y
183746AMRTJun-2197684 
193746CASHJun-2104 
203746INTJun-2132454 
223746ROUJun-21234564 
223746ROUJun-2130594 
633746L<1Jun-21469474Y
213746L<1Jun-214614 
213746L<1Jun-214514 
633746L>1Jun-21277274Y
213746L>1Jun-2126004 
213746L>1Jun-2126004 

 

Thanks in advance.

Regards

Ankhi

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

5 Replies
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

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

View solution in original post

Ankhi
Creator
Creator
Author

Thanks a lot Kushal. It is working.

Ankhi
Creator
Creator
Author

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