Bad Left Join + Where

[Anonymous]

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?

[marcus_sommer · Accepted solution]

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

[PrashantSangle]

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

[Anonymous]

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.

[Anonymous]

Thanks Marcus! Noconcatenate solved my question!