Join creates unnecessary duplication

Fri, 07 Jan 2011 10:45:50 GMT

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?

topic Join creates unnecessary duplication in QlikView

Join creates unnecessary duplication