FirstSortedValue Question

[#1]

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

[giakoum]

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;

[#3]

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]

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]

a sample would help

[#6]

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]

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]

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