FirstSortedValue

[rahulsingh12]

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

[tresesco]

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

[masismykola]

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

[rahulsingh12]

hi,

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

Regards,

Rahul

[rahulsingh12]

Hi,

Can I write this at script level.

Regards,

Rahul

[tresesco]

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

[masismykola]

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;

[Anonymous]

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;

[sunny_talwar]

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;