topic Re: Obtaining last field based on max(date) in same line in QlikView

Obtaining last field based on max(date) in same line

Thu, 26 Jul 2012 10:50:40 GMT

I'm producing a report that details the last payment made by a customer.

My tables are:

Customer Table: Customer ID, Customer name
Transactions Table: UniqueID, Customer ID, Transaction Type, Transaction Date, Transaction Amount, Transaction Method

The data I'm trying to produce is a list of customers and their last payment amount, date and method. A payment is a transaction type.

I've managed to obtain the last payment amount and date but am struggling to get the method.

I obtained the last payment date with the below formula in the script, loading the transaction table and grouping by Customer ID

"(if(Transaction Type = 'Payment', max(TransactionDate)) as [Last Payment Date]"

I then got the last payment amount with the following expression in the pivot table with Customer ID as my dimension. However I can see that there may be a flaw with this if 2 payments were to be received in the same day.

"sum(if(TransactionDate=[Last Payment Date] and [Transaction Type]='Payment', NetTransactionAmount) "

So my questions are:

Is there a better way of getting the last payment amount?
How do I get the last payment method?

The unique ID for transactions increases numerically in chronological order so this may be useful for a solution.

Re: Obtaining last field based on max(date) in same line

Thu, 26 Jul 2012 11:12:06 GMT

I'd solve it in the script by adding a "last transaction indicator".

Something like:

JOIN (Transactions) 
LOAD max([UniqueID],
           1 As LastTransaction
Resident Transactions
Group by Customer ID,
              [Transaction Type]

This will result in a 1 in the LastTransaction column for the last transaction of each type made by the customer. All other records will have a NULL value in that column.

I'm assuming your UniqueID is an incremental number (so the most recent transaction has the highest UniqueID).

Re: Obtaining last field based on max(date) in same line

brenner_martina — Thu, 26 Jul 2012 11:15:50 GMT

Hi,

if you have this new field in your script created

"(if(Transaction Type = 'Payment', max(TransactionDate)) as [Last Payment Date]"

you can use this expression in the chart:

Sum({<[Translation Type] = {"Payment"} , TransactionDate={"$(=Only([Last Payment Date]))"}>} NetTransactionAmount)

Greetings from Munich

Martina

Re: Obtaining last field based on max(date) in same line

Thu, 26 Jul 2012 14:04:51 GMT

dick

I tried that solution but with nearly 2 million rows of data in the transactions table the script is taking a long time to execute. So far 30 minutes has elapsed as opposed to the usual 6 minutes and it's still not finished so I don't think this is a workable solution but thanks for your suggestion.

Martina

That's worked, many thanks

Re: Obtaining last field based on max(date) in same line

brenner_martina — Thu, 26 Jul 2012 14:18:19 GMT

Hi,

I think there is something wrong, that's not normal! I thought, you just have the field Maxdate in your script entered! I gave you only a possible expression!

Greetings from Munich

Martina

Re: Obtaining last field based on max(date) in same line

Thu, 26 Jul 2012 14:27:08 GMT

Hi Maria,

Your solution worked. My other comments were in response to dickzeeman's suggestion.

Re: Obtaining last field based on max(date) in same line

brenner_martina — Thu, 26 Jul 2012 14:30:25 GMT

FINE!