I have the need to create a table where for each customer there is a row for every day they played along with the date of their previous play.
So the table will look like:
Id, Date1, Date2
The purpose of this is to define on each play whether the customer is new, reactivated, lapsed or existing based on business definitions on the interval between date1 and date2.
I've tried several methods for this, one being creating a single QVD with fields CustomerId, rownumber, rownumber2 (which is rownumber+1), date. I also tried a version where CustomerId and rownumber are contatenated (in the SQL query) to make crmid (and crmid2) which would be [Customerid]-[rownumber] (i.e. 1234-1, 1234-2, etc).
What I have tried to do is join as below:
CRM_1:
LOAD crmid, date
as date1
FROM $(vQVDPath)MeccaCRM.qvd (qvd);
LEFT JOIN
LOAD crmid2
as crmid, date
as date2
FROM $(vQVDPath)MeccaCRM.qvd (qvd);
This logic works fine in SQL Server and I get a table unique on crmid with date1 and date2, but in QlikView it produces random duplication and I can't work out why. I have tried using two separate QVDs, loading distinct, NOCONCATENATE functions but I always end up with duplicates, specifically when the the rownumber is less than 10.
Any ideas?
