Issue with FirstSortedValue

[Black_Hole]

Hello all,

I am using the function firstsortedvalue in my script.

In my script below, I apply the item type for each invoice sorted by the amount:

NoConcatenate
[RESULT_TEMP]:
Load Distinct
                         InvoiceNumber,
                        CustomerNumber,
                        FirstSortedValue(ItemType,-AmountCurrency)) AS ItemTypeTemp,
                        sum(AmountCurrency) as TotalAmountInvoice
Resident CUSTOMER_TEMP
Group By InvoiceNumber, CustomerNumber; 

But, I noticed, that if in an invoice there is many rows having the same amount then the FirstSortedValue is not applied and consequently the value of the ItemTypeTemp is empty.

For example:

>Input

CustomerNumber |InvoiceNumber | AmountCurrency | ItemType

A                                    | INV01                   | 120                              | S

A                                    | INV01                   | 20                                 | V

B                                    | INV02                   | 120                              | S

B                                    | INV02                   | 20                                | S

C                                    | INV03                   | 120                              | S

C                                    | INV03                   | 120                              | S

>Output

CustomerNumber |InvoiceNumber | TotalAmountInvoice| ItemTypeTemp

A                                    | INV01                   | 140                              | S

B                                    | INV02                   | 140                              | S

C                                    | INV03                   | 240                              | -

>> Normally for the third row of the table above, the result expected for ItemTypeTemp is "S". 

Please could you help me to resolve this issue.

Thank you in advance for your help.

[tresesco · Accepted solution]

You could try putting distinct identifier in the function like:

FirstSortedValue(Distinct ItemType,-AmountCurrency)) AS ItemTypeTemp 

If all of your item types are same for tied amount currency, your issue will be resolved. 

[Black_Hole]

Hello @tresesco 

I confirm after adding the Distinct,  my issue has been resolved.

Many thanks for your help!!