topic Re: Firstsortedvalue with load order in QlikView

Firstsortedvalue with load order

Anonymous — Fri, 29 Apr 2016 19:01:08 GMT

Hello,

Is it possible to use a field in the firstsortedvalue function with a weight of it's load order?

Thank you in advance.

Re: Firstsortedvalue with load order

sunny_talwar — Fri, 29 Apr 2016 19:04:13 GMT

If you have created a field like RowNo() as Key, you should be able to use Key as a load order. Is this what you were looking for, or did I mis-understand?

Re: Firstsortedvalue with load order

sunny_talwar — Fri, 29 Apr 2016 22:04:04 GMT

Another option is to use the Aggr() functions load order capability here. (This wouldn't require any modification in the script)

Script

Table:

LOAD * Inline [

Country, Value

India, 10

India, 15

India, 12

India, 16

USA, 12

USA, 30

USA, 4

USA, 49

USA, 2

China, 23

China, 40

];

Greens are the last value by load order and Red are the first value by load order

Straight table

Dimension

Country

Expression

1) =FirstSortedValue(Value, -Aggr(RowNo(TOTAL), Value, Country)) -> Green Values

2) =FirstSortedValue(Value, Aggr(RowNo(TOTAL), Value, Country)) -> Red Values

Re: Firstsortedvalue with load order

pover — Fri, 01 Apr 2022 13:01:50 GMT

One caveat with this approach is that if the Value field in the above example has duplicates, the load order is determined by the global load order and not the load order sliced by each country. So this will really only work when the Value field values are unique.