Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Black_Hole
Contributor 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
MVP
MVP

Re: Group By Max amount

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
MVP
MVP

Re: Group By Max amount

Try like:

InputTable:

Load * From <>;

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

View solution in original post

Black_Hole
Contributor II

Re: Group By Max amount

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.