topic Trying to get rid of a Cartesian join in QlikView

Trying to get rid of a Cartesian join

Fri, 16 Nov 2012 13:58:14 GMT

I have a 2 tables - One returns 24000 Transaction Lines and a second returns 1500 lines from a Financial Calendar QVD file. When I left join the Calendar table to the transaction table I get 24000*1500 transactions. I have compared the underlying data I have perfect matches. I have exported the table seperately to excel and performed a VLOOKUP function and get a match however when I perform a Left Join in QV I get a Cartesian join. Any help is appreciated

Table

Script

Sample Data Returned

Transaction

Trans:

Load

TPROD AS POLineItem,

TTDTE AS TransactionDate;

SELECT * FROM $(vSystem).$(vTable) WHERE (TTYPE = 'U');

POLineItem	TransactionDate
3110.374	20101108
3110.374	20101122
3110.374	20110110
3110.374	20110127
3110.374	20110203
3110.374	20110217
3110.374	20110311
3110.374	20110314
3110.374	20110405
3110.374	20110520

Calendar

Calendar:

LEFT JOIN (Trans) LOAD

Date as TransactionDate,

FinanceYearPeriod;

SELECT * FROM c:\Qlikview\QVDs\Calendar.qvd;

TransactionDate	FinanceYearPeriod
20090406	201001
20090407	201001
20090408	201001
20090409	201001
20090410	201001
20090411	201001
20090412	201001
20090413	201001

Re: Trying to get rid of a Cartesian join

gandalfgray — Fri, 16 Nov 2012 14:03:12 GMT

I don't know if it helps, but I would skip the Calendar: prefix,

and get rid of the select * when reading from a qvd

like this:

Trans:

Load

TPROD AS POLineItem,

TTDTE AS TransactionDate;

SELECT * FROM $(vSystem).$(vTable) WHERE (TTYPE = 'U');

LEFT JOIN (Trans) LOAD

Date as TransactionDate,

FinanceYearPeriod

FROM c:\Qlikview\QVDs\Calendar.qvd;

Re: Trying to get rid of a Cartesian join

MayilVahanan — Fri, 16 Nov 2012 14:09:06 GMT

Try like this

Trans:

Load

TPROD AS POLineItem,

TTDTE AS TransactionDate;

SELECT * FROM $(vSystem).$(vTable) WHERE (TTYPE = 'U');

LEFT JOIN (Trans) LOAD

Date as TransactionDate,

FinanceYearPeriod;

FROM c:\Qlikview\QVDs\Calendar.qvd(qvd);

Re: Trying to get rid of a Cartesian join

Fri, 16 Nov 2012 14:49:42 GMT

Many thanks for your help. It worked this way.

Paul

Re: Trying to get rid of a Cartesian join

gandalfgray — Fri, 16 Nov 2012 14:59:47 GMT

Wouldn't it be better to credit the correct answer?

Mayil's answer has a syntax error,

mine has not.

Gandalf