Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

FirstSortedValue not working

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";

2 Replies
ahaahaaha
Honored Contributor

Re: FirstSortedValue not working

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

nvrphanikumar
New Contributor III

Re: FirstSortedValue not working

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.