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
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;
I have attached a spreadsheet with some dummy data. I need to come up with a straight table with just the values in green, i.e. the final balances on that customer.
Many thanks
H
What is the criteria to select values in green? Is it based on max TransactionID? Then try this
LOAD CustomerID,FirstSortedValue(TransactionID,-TransactionID) As TransactionID,FirstSortedValue(CustomerBalance,-TransactionID) As CustomerBalance,
FirstSortedValue(Date,-TransactionID) 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 ];
Not on transaction ID, but on date, i.e. last transaction on that date i.e. max date
If there are multiple transactions on the same day, which transaction is to be returned by the script?
If you have sorted the data in the order you want, you may try using FirstValue() function.
Hi Herbert,
why dont you try with Peek function to fectch the last entries of each Customer ID?
Regards
KC
Do you have time along with transaction date?
Hi Anbu, no I have not imported time, but I could I guess, cause I am working with a time stamp
sorry, no this particular data doesn't have a time stamp.
Without time how will you identify final balances?