topic Re: FirstSortedValue in QlikView

FirstSortedValue

rahulsingh12 — Fri, 18 Aug 2017 10:08:01 GMT

Hi,

Can some one give me a simple example of how to use firstsortedvalue in Qlikview script.

I want this function to come as a new column in the load statement of the script.

Regards,

Rahul

Re: FirstSortedValue

tresB — Fri, 18 Aug 2017 10:14:36 GMT

Many such discussions are there in community here. You have to just search: FirstSortedValue(), or search help:FirstSortedValue - chart function ‒ QlikView

Re: FirstSortedValue

masismykola — Fri, 18 Aug 2017 10:16:31 GMT

Hi, lets say you want to display supplier with highest sales

Data:

LOAD * INLINE [

Supplier, Sales

ABC-Company, 10

ABC-Company, 5

XYZ-Company, 4

ABC-Company, 5

XYZ-Company, 8

123-Company, 15

];

Use this in text box as KPI:

=FirstSortedValue(Supplier,-aggr(sum(Sales),Supplier))

1st parameter = what you want to display
2nd parameter = by which value you want it to sort (to sort from highest you must put minus sign in front)

the result will be ABC-Company, because its total sales is 20

Re: FirstSortedValue

rahulsingh12 — Fri, 18 Aug 2017 10:34:21 GMT

hi,

I am getting an error if I try to write in script.

Regards,

Rahul

Re: FirstSortedValue

rahulsingh12 — Fri, 18 Aug 2017 10:34:47 GMT

Hi,

Can I write this at script level.

Regards,

Rahul

Re: FirstSortedValue

tresB — Fri, 18 Aug 2017 10:41:15 GMT

To write in script, one common fact to remember is - it's an aggregation function and probably missing a Group By clause. Check: FirstSortedValue - script function

Re: FirstSortedValue

masismykola — Fri, 18 Aug 2017 11:04:47 GMT

Hi sorry I didnt read carefully that evarything must be in script. Here is example of using it in script:
You will get Products with smallest orders per Customer

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue:

LOAD Customer,FirstSortedValue(Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Re: FirstSortedValue

Anonymous — Fri, 18 Aug 2017 15:27:42 GMT

Hi,

Its working perfectly for ascending order of firstsortedvalue, but it showing blank for Divadip if i go for descending order. So i just changed the unit sales values to 17 in the example to work in both orders.

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|17|4

Divadip|DD|11|10|4

] (delimiter is '|');

Ascending order

FirstSortedValue:

LOAD Customer,FirstSortedValue(Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Descending order

FirstSortedValue:

LOAD Customer,FirstSortedValue(Product, -UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer;

Re: FirstSortedValue

sunny_talwar — Fri, 18 Aug 2017 16:38:42 GMT

May be add a DISTINCT

Temp:

LOAD * inline [

Customer|Product|OrderNumber|UnitSales|CustomerID

Astrida|AA|1|10|1

Astrida|AA|7|18|1

Astrida|BB|4|9|1

Astrida|CC|6|2|1

Betacab|AA|5|4|2

Betacab|BB|2|5|2

Betacab|DD|12|25|2

Canutility|AA|3|8|3

Canutility|CC|13|19|3

Divadip|AA|9|16|4

Divadip|AA|10|16|4

Divadip|DD|11|10|4

] (delimiter is '|');

FirstSortedValue:

LOAD Customer,

FirstSortedValue(DISTINCT Product, UnitSales) as MyProductWithSmallestOrderByCustomer,

'Asc' as Flag

Resident Temp Group By Customer;

FirstSortedValue:

LOAD Customer,

FirstSortedValue(DISTINCT Product, -UnitSales) as MyProductWithSmallestOrderByCustomer,

'Desc' as Flag

Resident Temp Group By Customer;