Compare two value

[Black_Hole]

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.

[Taoufiq_Zarra · Accepted solution]

@Black_Hole 

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

[Black_Hole]

Hello @Taoufiq_Zarra ,

Excellent !! Your solution works as expected.

Thanks a lot for your help.