Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Black_Hole
Creator II
Creator II

Group By Max amount

Hello all,

I'm facing a problem with the logic behind my script.

I would like to create a new column "NewCodeSystem".

This new column is equal to the result of the value CodeSystem which has the maximum Amount and grouped by invoice number.

To explain this logic below an example:

Current table:

NumInvoice  |   Amount   |  CodeSystem

19105               |     200          |   10  

19105               |     5              |    11

19105               |     6              |    1

18106               |     50            |    52

18106               |     600          |   51

Result expected:

NumInvoice  |   Amount   |  NewCodeSystem

19105               |     200          |   10  

19105               |     5              |    10

19105               |     6              |    10

18106               |     50            |    51

18106               |     600          |   51

I don't know if it's possible to do that in QVW.

Please could you tell me if it's possible.

Thank you in advance for your help.

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

InputTable:

Load * From <>;

Join
Output:
Load
         NumInvoice,
         FirstSortedValue(CodeSystem, -Amount) as NewCodeSystem
Resident InputTable Group By NumInvoice;

View solution in original post

2 Replies
tresesco
MVP
MVP

Try like:

InputTable:

Load * From <>;

Join
Output:
Load
         NumInvoice,
         FirstSortedValue(CodeSystem, -Amount) as NewCodeSystem
Resident InputTable Group By NumInvoice;

Black_Hole
Creator II
Creator II
Author

Hello @tresesco ,

Excellent! I tried it and I find the expected result.

I didn't know this function, until your reply, I will keep it preciously in my mind.

 

Many thanks for your help.