FirstSortedValue not working

[#1]

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

[ahaahaaha]

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]

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.