topic Re: FirstSortedValue Question in QlikView

FirstSortedValue Question

Thu, 09 Oct 2014 09:36:15 GMT

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.

Re: FirstSortedValue Question

Thu, 09 Oct 2014 10:44:49 GMT

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

Re: FirstSortedValue Question

anbu1984 — Thu, 09 Oct 2014 10:55:01 GMT

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

Re: FirstSortedValue Question

Thu, 09 Oct 2014 11:07:13 GMT

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

Re: FirstSortedValue Question

nagaiank — Thu, 09 Oct 2014 11:08:45 GMT

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.

Re: FirstSortedValue Question

jyothish8807 — Thu, 09 Oct 2014 11:08:56 GMT

Hi Herbert,

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

Regards

Re: FirstSortedValue Question

anbu1984 — Thu, 09 Oct 2014 11:10:45 GMT

Do you have time along with transaction date?

Re: FirstSortedValue Question

Thu, 09 Oct 2014 11:28:44 GMT

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

Re: FirstSortedValue Question

Thu, 09 Oct 2014 11:29:26 GMT

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

Re: FirstSortedValue Question

anbu1984 — Thu, 09 Oct 2014 11:48:27 GMT

Without time how will you identify final balances?

Re: FirstSortedValue Question

Thu, 09 Oct 2014 11:51:39 GMT

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

Re: FirstSortedValue Question

Thu, 09 Oct 2014 11:52:31 GMT

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

Re: FirstSortedValue Question

manuelreimitz — Thu, 09 Oct 2014 12:02:55 GMT

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;

Re: FirstSortedValue Question

anbu1984 — Thu, 09 Oct 2014 12:03:25 GMT

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

Re: FirstSortedValue Question

jyothish8807 — Thu, 09 Oct 2014 12:09:22 GMT

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

Re: FirstSortedValue Question

Thu, 09 Oct 2014 13:48:24 GMT

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

Re: FirstSortedValue Question

anbu1984 — Thu, 09 Oct 2014 14:01:05 GMT

Did you try LastValue()?

Re: FirstSortedValue Question

Thu, 09 Oct 2014 14:58:56 GMT

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