Issue with FirstSortedValue

Black_Hole — Sat, 16 Nov 2024 00:22:42 GMT

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

>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.

Re: Issue with FirstSortedValue

tresB — Fri, 03 Jul 2020 10:48:18 GMT

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.

Re: Issue with FirstSortedValue

Black_Hole — Fri, 03 Jul 2020 11:03:01 GMT

Hello @tresB

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

Many thanks for your help!!

topic Re: Issue with FirstSortedValue in QlikView

Issue with FirstSortedValue

Re: Issue with FirstSortedValue

Re: Issue with FirstSortedValue