Firstsortedvalue

.Qlikview Help Definition:

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting thesort_weight argument, taking into account rank, if specified. If more than one resulting value shares the same sort_weightfor the specified rank, the function returns NULL.

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;

firstsortedvalue1.PNG

firstsortedvalue2.PNG

 

  1. How to add [TM Event Name] = ‘Metal Wear Reading’ condition to the below FirstSortedValue() expression?
    =FirstSortedValue([TM Event Value], -[TM Event Date])

    =FirstSortedValue({<[TM Event Name] = {‘Metal Wear Reading’}>}[TM Event Value], -[TM Event Date])

    2. test:

    LOAD * INLINE [

    employee, time, status,rank

    Fred, 09:00, In, 1

    Fred, 09:55, Out, 2

    Jo, 08:55, In, 1
    Bert, 08:40, In, 1
    Bert, 12:40, Out, 2
    Bert, 14:40, In, 3

    ];
    LOAD
    employee as employeenew,
    max(time(time,’hh:mm’)) as maxtime ,
    FirstSortedValue(status,-time(time,’hh:mm’)) as statusnew,
    FirstSortedValue(rank,-time(time,’hh:mm’)) as ranknew
    Resident test
    Group by employee;
    DROP Table test;

     

    3.I have 2 columns, Sales and Date.
    Using the below expression to get the latest enrty from those 2 columns.

    firstSortedValue(Sales,-Date)

    This is working fine if the expression returns one Date value.
    But when there are 2 latest Dates, it does not return anything.
    How do I sum the Sales when there are multiple entries?

    To avoid summing of Sales for multiple Companies

    FirstSortedValue(Aggr(Sum(Sales), Date, Company), -Aggr(Date,Date, Company))

4.  Avg({<VISIT = {“$(=FirstSortedValue(DISTINCT VISIT,-Date))”}>}Value)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.