topic Re: FirstSortedValue not working in QlikView

FirstSortedValue not working

Mon, 20 Feb 2017 10:34:49 GMT

Can anyone tell me why the below does not always work.

Some codes are not given a "latest-gross-cost" even though there is always at least one price loaded.

Thanks for any help

ProductPrice:

LOAD

"gross-cost",

Text("p-code") as "p-code",

"pr-date";

SQL SELECT

"gross-cost",

"p-code",

"pr-date"

FROM PUB.price;

INNER JOIN (ProductPrice)

LOAD

"p-code",

Date(Max("pr-date")) as "pr-date-max",

FirstSortedValue("gross-cost",-"pr-date") as "latest-gross-cost"

Resident ProductPrice

Group by "p-code";

Re: FirstSortedValue not working

ahaahaaha — Tue, 21 Feb 2017 07:34:42 GMT

Hi,

FirstSortedValue - script function

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, taking into account rank, if specified. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.

Maybe this is the case (highlighted in red)?

Regards,

Andrey

Re: FirstSortedValue not working

nvrphanikumar — Wed, 12 Sep 2018 16:39:45 GMT

Thanks for the hint.

troubleshooting an issue i had and i used DISTINCT keyword to solve it.

FirstSortedValue( DISTINCT "gross-cost",-"pr-date")

I believe,Its picking random value out of same rank.