Qlik Community

QlikView Documents

Documents for QlikView related information.

Using FirstSortedValue in the Script

Luminary
Luminary

Using FirstSortedValue in the Script

Hi,

I have been looking into FirstSortedValue funtion within a script. Here is an example which uses the function within a loop transforming the original data to either one with flags or a new dimension feild.

Original Data

Picture1.jpg


Added Flags

Picture2.jpg


New Dimension

Picture3.jpg

I will upload a more detailed walkthough on my blog soon (www.qlikCentral.com).

For a good examlpe of using the funcion within the user interface please see this blog "Straight (and Pivot) Tables - more food for thought" (http://community.qlik.com/docs/DOC-6046) by Jason Michaelides

Richard

Attachments
Comments
manojkvrajan
Contributor III

Good one Richard.

Luminary
Luminary

I've uploaded the more detailed walkthough here

http://qlikcentral.com/2014/08/13/firstsortedvalue/

Thanks

Richard

0 Likes
Not applicable

Helpfull..

0 Likes
Not applicable

Hi,
This looks super useful.  Unfortunately I'm on QlikSense desktop and and get an error message when trying to open wiht Qlikview personal edition...(it only can open files created by me).  Any chance to see how you handled this in the script or a qvf file?

Thank you!

0 Likes
Luminary
Luminary

Here you go Pia:

Customers_Temp:
Load * Inline [
ID  ,Customer ,Product ,DayOfSale
1  ,James  ,Shoes  ,5
2  ,James  ,Jeans  ,7
3  ,James  ,Hat  ,2
4  ,James  ,Coat  ,4
5  ,Richard ,Shoes  ,8
6  ,Richard ,Jeans  ,9
7  ,Richard ,Hat  ,6
8  ,Richard ,Coat  ,2
9  ,Richard ,Shoes  ,4
10  ,Richard ,Jeans  ,5
];

// Find out what the max count of Distint DayOfSale **************************************************************
CountDayOfSale_Temp:
Load
count(DISTINCT DayOfSale) as CountDayOfSale
Resident Customers_Temp
group by Customer;

Max_CountDayOfSale:
Load Max(CountDayOfSale) as Max_CountDayOfSale Resident CountDayOfSale_Temp;
// **************************************************************************************************************


// Loop through the data and created the firstsorted value flag **************************************************
For vRank = 1 to Peek('Max_CountDayOfSale',0,'Max_CountDayOfSale');

left Join (Customers_Temp)
Load
  FirstSortedValue(ID,DayOfSale,$(vRank)) as ID
  ,if(FirstSortedValue(Product,DayOfSale,$(vRank))<>null(),1,0)  as _FLAG_BuyingOrder$(vRank)
Resident Customers_Temp
Group by Customer;

Next vRank
// **************************************************************************************************************


// Change the null values to zero ******************************************************************************
MapNulls:
MAPPING LOAD null(), 0 AUTOGENERATE 1;

Map * using MapNulls;

Customers:
NoConcatenate LOAD * Resident Customers_Temp;
// **************************************************************************************************************


Drop Table Customers_Temp;

// Create a new table for this example
Customer_B_Temp:
Load * Inline [
B.ID ,B.Customer ,B.Product ,B.DayOfSale
1  ,James  ,Shoes  ,5
2  ,James  ,Jeans  ,7
3  ,James  ,Hat  ,2
4  ,James  ,Coat  ,4
5  ,Richard ,Shoes  ,8
6  ,Richard ,Jeans  ,9
7  ,Richard ,Hat  ,6
8  ,Richard ,Coat  ,2
9  ,Richard ,Shoes  ,4
10  ,Richard ,Jeans  ,5
];

// Create a default buying order
Customer_B_Temp1:
Load *
,0 as B.Buying_Order
Resident Customer_B_Temp;


// Loop through the data and created the firstsorted Buying Order **************************************************
For vRank = 1 to Peek('Max_CountDayOfSale',0,'Max_CountDayOfSale');

Customer_B:
Load
  FirstSortedValue(B.ID,B.DayOfSale,$(vRank)) as B.ID
  ,if(FirstSortedValue(B.Product,B.DayOfSale,$(vRank))<>null(),$(vRank),0)  as B.Buying_Order
Resident Customer_B_Temp1
Where B.Buying_Order<>1
Group by B.Customer;

Next vRank

// Clean up buy removing and Buying_Order's the are zero and join to to original data
Left Join (Customer_B_Temp)
LOAD * Resident Customer_B
where B.Buying_Order>0;


DROP Table Customer_B;
Drop Table Customer_B_Temp1;

Luminary
Luminary

Here is the Blog Post that relates to this document:

https://qlikcentral.com/2014/08/13/firstsortedvalue/

0 Likes
Not applicable

Awesome!!  Thank you very much Richard.

Can't wait to try this out now.

"If I have seen further, it is by standing on the shoulders of giants."

Isaac Newton

subhash_gherade
New Contributor III

please find may helpful

FirstSortedValue - chart function

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, for example, the name of the product with the lowest unit price. The nth value in the sort order, can be specified in rank. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.

Syntax:

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, sort_weight [,rank])

Return data type: dual

Arguments:

ArgumentDescription
valueOutput field. The function finds the value of the expression value that corresponds to the result of sorting sort_weight.
sort_weight

Input field. The expression containing the data to be sorted. The first (lowest) value of sort_weight is found, from which the corresponding value of the value expression is determined. If you place a minus sign in front of sort_weight, the function returns the last (highest) sorted value instead.

rank

By stating a rank "n" larger than 1, you get the nth sorted value.

SetExpressionBy default, the aggregation function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a set analysis expression.
DISTINCTIf the word DISTINCT occurs before the function arguments, duplicates resulting from the evaluation of the function arguments are disregarded.
TOTAL

If the word TOTAL occurs before the function arguments, the calculation is made over all possible values given the current selections, and not just those that pertain to the current dimensional value, that is, it disregards the chart dimensions.

By using TOTAL [<fld {.fld}>], where the TOTAL qualifier is followed by a list of one or more field names as a subset of the chart dimension variables, you create a subset of the total possible values.

See: Defining the aggregation scope

Examples and results:

CustomerProductUnitSalesUnitPrice
AstridaAA416
AstridaAA1015
AstridaBB99
BetacabBB510
BetacabCC220
BetacabDD-25
CanutilityAA815
CanutilityCC-19
ExampleResult
firstsortedvalue (Product, UnitPrice)

BB, which is the Productwith the lowest UnitPrice(9).

firstsortedvalue (Product, UnitPrice, 2)

BB, which is the Productwith the second-lowest UnitPrice(10).

firstsortedvalue (Customer, -UnitPrice, 2)

Betacab, which is the Customerwith the Product that has second-highest UnitPrice(20).

firstsortedvalue (Customer, UnitPrice, 3)

NULL, because there are two values of Customer (Astrida and Canutility) with the samerank (third-lowest) UnitPrice(15).

Use the distinct qualifier to make sure unexpected null results do not occur.

firstsortedvalue (Customer, -UnitPrice*UnitSales, 2)

Canutility, which is the Customer with the second-highest sales order value UnitPricemultiplied by UnitSales (120).

Data used in examples:

ProductData:

LOAD * inline [

Customer|Product|UnitSales|UnitPrice

Astrida|AA|4|16

Astrida|AA|10|15

Astrida|BB|9|9

Betacab|BB|5|10

Betacab|CC|2|20

Betacab|DD||25

Canutility|AA|8|15

Canutility|CC||19

] (delimiter is '|');

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-07-22 08:02 AM
Updated by: