topic Re: Group By Max amount in QlikView

Group By Max amount

Black_Hole — Sat, 16 Nov 2024 02:07:44 GMT

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.

tresB — Thu, 24 Oct 2019 07:42:26 GMT

Try like:

InputTable:

Load * From <>;

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

Black_Hole — Thu, 24 Oct 2019 12:19:32 GMT

Hello @tresB ,

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.