topic Re: Bad Left Join + Where in QlikView

Bad Left Join + Where

Anonymous — Wed, 13 Aug 2014 14:48:40 GMT

I want to translate this sql below to qlikview extracting the data from these two qvds: TABLE1.QVD and TABLE2.QVD

TEST:

SQL SELECT HST.*, CFC.INICIO,CFC.FIM

FROM TABLE1, TABLE2

WHERE TABLE1.CATG_ID=TABLE2.CATG_ID

AND TABLE1.QTCONSUMO BETWEEN TABLE2.INICIO AND TABLE2.FIM;

STORE TEST INTO TEST.QVD(QVD);

DROP TABLE TEST;

I know i can use left join, but the problem is that the "where" condition uses fields from both qvds.

Anyone help me?

Re: Bad Left Join + Where

marcus_sommer — Wed, 13 Aug 2014 15:30:35 GMT

Complex where-conditions are difficult to impossible but you could use a multi-step method with load + join and in a second step make the where-condition

Test1:

Load CATG_ID, OtherFields From TABLE1.QVD (QVD);

join

Load CATG_ID, OtherFields From TABLE2.QVD (QVD);

Test2:

Noconcatenate Load * Resident Test1 Where YourCondition;

Alternatively for many cases you could use mapping with applymap() to build your tables.

- Marcus

Re: Bad Left Join + Where

PrashantSangle — Wed, 13 Aug 2014 15:48:07 GMT

Hi,

Try this,

TEST:

Load CATG_ID,

....

From TABLE1.QVD (QVD);

join

Load CATG_ID,

.....

From TABLE2.QVD (QVD);

FINAL_TEST:

Load * Resident TEST

Where QTCONSUMO>=INICIO AND QTCONSUMO<=FIM;

STORE FINAL_TEST INTO TEST.QVD(QVD);

DROP TABLE TEST;

DROP TABLE FINAL_TEST;

Regards

Re: Bad Left Join + Where

Anonymous — Wed, 13 Aug 2014 18:12:19 GMT

Thanks max!

but your way didnt work, because the FINAL_TEST table doesn't exist. So i have to use NOCONCATENATE to force qlikview doesn't mix tables.

Re: Bad Left Join + Where

Anonymous — Wed, 13 Aug 2014 18:13:14 GMT

Thanks Marcus! Noconcatenate solved my question!