Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
giakoum
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;

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

prieper
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

giakoum
Partner
Partner

a sample would help

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

sasikanth
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

prieper
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