Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

FirstSortedValue Question

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

1 Solution

Accepted Solutions
manuelreimitz
Partner - Contributor III
Partner - Contributor III

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;

View solution in original post

17 Replies
Not applicable
Author

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
Master III
Master III

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 applicable
Author

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

nagaiank
Specialist III
Specialist III

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
Master II
Master II

Hi Herbert,

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

Regards

KC

Best Regards,
KC
anbu1984
Master III
Master III

Do you have time along with transaction date?

Not applicable
Author

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

Not applicable
Author

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

anbu1984
Master III
Master III

Without time how will you identify final balances?