topic Re: FirstSortedValue Question in QlikView

FirstSortedValue Question

Wed, 25 Mar 2015 12:38:38 GMT

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

Re: FirstSortedValue Question

giakoum — Wed, 25 Mar 2015 13:00:06 GMT

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;

Re: FirstSortedValue Question

Wed, 25 Mar 2015 13:07:11 GMT

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

Re: FirstSortedValue Question

prieper — Wed, 25 Mar 2015 13:46:41 GMT

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

Re: FirstSortedValue Question

giakoum — Wed, 25 Mar 2015 14:07:35 GMT

a sample would help

Re: FirstSortedValue Question

Wed, 25 Mar 2015 14:16:40 GMT

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

Re: FirstSortedValue Question

sasikanth — Wed, 25 Mar 2015 14:21:21 GMT

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

Re: FirstSortedValue Question

prieper — Wed, 25 Mar 2015 14:24:57 GMT

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