Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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:

  1. Is there a better way of getting the last payment amount?
  2. 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.

6 Replies
Not applicable

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

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).

brenner_martina
Valued Contributor II

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

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

Not applicable

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

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

brenner_martina
Valued Contributor II

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

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

Not applicable

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

Hi Maria,

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

brenner_martina
Valued Contributor II

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

FINE!

Community Browser