FirstSortedValue Question

[#1]

Hi All,

I have a list of customer transactions with columns say Date, Customer ID, Sales Value, Statement Balance etc. For each client, I will have multiple transactions, and sometimes multiple transactions on the same day. In my load script, I have sorted in ascending order Customer ID and then Date.

I am trying to prepare a straight table to Statement balance for each customer. Can anyone assist with the expression I could use in such a table. Firstsortedvalue wont work because there will be multiple transactions for a given client on the same day.

Thank u.

H

[#2]

Peek function would work, can you illustrate how I would use it?

[#3]

Its the last customer entry for that customer ID. Data has already been sorted, so the last transaction will be ok

[manuelreimitz · Accepted solution]

BaseData2:

LOAD

     _customerid as customerid,

     _date as date,     

     _value value,

     if(_customerid <> peek('customerid'), 1, 0) as laststateflag

RESIDENT BaseData1

ORDER BY _customerid asc, _date desc;

LastState:

LOAD

     customerid,

     date as laststatedate,

     value as laststate

RESIDENT BaseData2

WHERE laststateflag = 1;

[anbu1984]

If data is already sorted in descending order, then try LastValue()

LOAD CustomerID,LastValue(TransactionID) As TransactionID,LastValue(CustomerBalance) As CustomerBalance,

LastValue(Date) As Date Group by CustomerID;

lOAD * Inline [

Date,CustomerID,TransactionID,CustomerBalance

1-Jan-14,Cus - 00,111,0

2-Jan-14,Cus - 00,212,5

3-Jan-14,Cus - 00,301,8

4-Jan-14,Cus - 00,432,5

4-Jan-14,Cus - 00,523,0

6-Jan-14,Cus - 01,645,5

7-Jan-14,Cus - 01,767,10

9-Jan-14,Cus - 01,843,15

9-Jan-14,Cus - 01,934,20

9-Jan-14,Cus - 01,102,15

11-Jan-14,Cus - 01,115,18

12-Jan-14,Cus - 02,128,20

13-Jan-14,Cus - 02,130,5 ];

[jyothish8807]

right, so peek bydefault fetch last entries.So i guess you have to group it with customer ID, so that you can get last entries.

Regards

KC

[#7]

Hi Manuel,

I think this is getting closer to what I need. Unfortunately the last state flag is coming up as 1 on all the transactions. My customer ID's are alphnumeric eg V126958. Will this affect the ordering?

Thanks

H

[anbu1984]

Did you try LastValue()?

[#9]

Many thanks, that was the magical bullet!!!!