Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try like:
InputTable:
Load * From <>;
Join
Output:
Load
NumInvoice,
FirstSortedValue(CodeSystem, -Amount) as NewCodeSystem
Resident InputTable Group By NumInvoice;
Try like:
InputTable:
Load * From <>;
Join
Output:
Load
NumInvoice,
FirstSortedValue(CodeSystem, -Amount) as NewCodeSystem
Resident InputTable Group By NumInvoice;
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.