Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am trying to affect the value "type" of the maximum sum by invoice.
To illustrate this idea, below an example:
Invoice num | Type | Amount
INV123 | S | 0
INV123 | F | 3 500
INV567 | S | 1 500
INV567 | F | 500
----> Result expected:
Invoice num | Type | Amount
INV123 | F | 3 500
INV567 | S | 2 000
Please could you help me to resolve this?
Many thanks in advance for your help.
May be :
Data:
load * inline [
Invoice num | Type | Amount
INV123 | S | 0
INV123 | F | 3500
INV567 | S | 1500
INV567 | F | 500
](delimiter is '|');
output:
noconcatenate
load [Invoice num], FirstSortedValue(Type,-Amount) as Type,sum(Amount) as Amount resident Data group by [Invoice num];
drop table Data;
attached qvw file
May be :
Data:
load * inline [
Invoice num | Type | Amount
INV123 | S | 0
INV123 | F | 3500
INV567 | S | 1500
INV567 | F | 500
](delimiter is '|');
output:
noconcatenate
load [Invoice num], FirstSortedValue(Type,-Amount) as Type,sum(Amount) as Amount resident Data group by [Invoice num];
drop table Data;
attached qvw file