Thanks for the response.If you could give me an example, it would be very usefull.
Also how will i get the transaction with the most recent date from the transactions table and then use IntervalMatch to match it to one of my predefined categories based on the date difference?
Actually, i solved the first part of my question without the IntervalMatch. I did it using an excel with the date difference and the value i want to display, and i created another field in my loading script that calculates the difference of days in days.
The second part is a bit difficult for me. How will i choose the most recent transaction for each customer?
Ok here it is a simple example.
As i said all i want is to find the most recent data from a series of data that have the same id.
qv1.qvw 157.2 K
I think the qvw i attached is self explanatory. I will include an excel file with the initial test data and the desired results. Kep in mind i just want a way to get all the transactions for eacd customer (ID) at the most recent date for each customer purchase.
For example if customer with ID=1 has 10 transactions in his transactions history and the date of his most recent transaction is August 4th, then for this user (for ID=1 ONLY) i want to get only the transactions for this date. It may be just one transaction or many . It does not matter.
Now if customer with ID=2 has his most recent transaction at July 28th i want to get all of his transactions (for ID=2 ONLY) at July 28th. One or many it does not matter.
Here's the load script that may help you, by attaching a flag to the max date for each customer:
id&date as Key
(ooxml, embedded labels, table is Sheet1);
LOAD id as id2,
Max(date) as MaxDate,
1 as flag,
id&Max(date) as Key2
Group by id;
Left Join (Table1)
LOAD Key2 as Key,
DROP Table Table2;
I also attach a qvd with the desired result on a table
Please let me know if it helped
Max Date for an ID.qvw 137.0 K
Hi there, thanks for your answer. It works fine!!!
No i want to create a table that shows the number of customers per number of transactions in a specified date.
For example if i choose August 6 as my date, how many Distinct users have 0 transactions for that date, how many 1 transaction, how many 2 transactions , etc. etc.?