Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

FirstSortedValue Question

Hi guys,

my data set look like this

Time Created                    Txn date          Txn

3/24/2015 20:16:49          12/02/2014      Invoice10

3/24/2015 18:23:12          12/02/2014     invoice 13

3/24/2015 20:13:08          03/24/2015     Invoice12

3/24/2015 21:12:09          03/24/2015     Invoice35

I need to find out the last and first invoice created on last and first txn date.

Thanks,

Saurabh

7 Replies
Highlighted
Partner
Partner

Note that if more than one value (as your case) share the same sort order, the function will return null.

You need a time stamp also to make it work.


firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

Highlighted
Not applicable

Hi ioannis,

I can not change the load script and everything has to be done in expression it self.

I have time stamp as i have mentioned in my question.

But somehow I am not able to get the expression working.

Saurabh

Highlighted
Master II
Master II

Convert the timestamp into numeric value and then use SET-expession:

LOAD

  *,

  NUM([Time Created]) AS Time_num;

LOAD

  TIMESTAMP(TIMESTAMP#([Time Created], 'M/D/YYYY hh:mm:ss')) AS [Time Created], [Txn date], Txn

  INLINE [

    Time Created,                    Txn date,          Txn

    3/24/2015 20:16:49,          12/02/2014,      Invoice10

    3/24/2015 18:23:12,          12/02/2014,     invoice 13

    3/24/2015 20:13:08,          03/24/2015,     Invoice12

    3/24/2015 21:12:09,          03/24/2015,     Invoice35

];

Expression then might be something like:

=ONLY( {$ <Time_num = {$(=MAX(Time_num)) }>} Txn)

Edith says: as you cannot change the script, the above will not work.

So you may create a variable:

vMaxTime to read NUM(MAX([Time Created]))

Then you may use a formula like

=ONLY( {$ <Time_num = {"$(vMaxTime)"}>} Txn)

HTH Peter

Highlighted
Partner
Partner

a sample would help

Highlighted
Not applicable

Hi Peter,

This may not work because because the first transaction may have the latest Time created but last Txn date. So basically what I need is to first sort by Txndate and then sort by Time Created to get the first transaction.

Saurabh

Highlighted
Specialist III
Specialist III

TAB:

load
*,
num(Timestamp(Timestamp#(TimeCreated,'MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss'))as T_Date

Inline [
TimeCreated , TxnDate , Txn

3/24/2015 20:16:49 , 12/02/2014 , Invoice10

3/24/2015 18:23:12 , 12/02/2014 , invoice 13

3/24/2015 20:13:08 , 03/24/2015 , Invoice12

3/24/2015 21:12:09 , 03/24/2015 , Invoice35

]
;

T:
LOAD
FirstSortedValue(Txn,T_Date) as TD
Resident TAB group by Txn ;
EXIT Script;

may be helpful

Highlighted
Master II
Master II

Please check the edit-version of my comment,

would work similar then with Txn-Date and will require some additional statement for the first, this goes analogue to this.

Peter