Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
danilodct
Contributor III
Contributor III

Bad Left Join + Where

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?

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

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

Great dreamer's dreams never fulfilled, they are always transcended.
danilodct
Contributor III
Contributor III
Author

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.

danilodct
Contributor III
Contributor III
Author

Thanks Marcus! Noconcatenate solved my question!