Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Peek function would work, can you illustrate how I would use it?
Its the last customer entry for that customer ID. Data has already been sorted, so the last transaction will be ok
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;
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 ];
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
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
Did you try LastValue()?
Many thanks, that was the magical bullet!!!!