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]

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

[anbu1984]

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 ];

[#4]

Not on transaction ID, but on date, i.e. last transaction on that date i.e. max date

[nagaiank]

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.

[jyothish8807]

Hi Herbert,

why dont you try with Peek function to fectch the last entries of each Customer ID?

Regards

KC

[anbu1984]

Do you have time along with transaction date?

[#8]

Hi Anbu, no I have not imported time, but I could I guess, cause I am working with a time stamp

[#9]

sorry, no this particular data doesn't have a time stamp.

[anbu1984]

Without time how will you identify final balances?