CODEWITHSUNDEEP
qlikview
W.K
W.K

Reputation: 75

Qlikview left join two table and generate the data to qvd

I'm relatively new to qlikview and not sure if the left join is the same as normal sql: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Would like to have your assistance to left join the tables using TRIP_ID, and classified the period using the ARRIVE_DT then generate the data in qvd 

LOAD "TRIP_ID",
    "PM_M",
    "ARRIVE_DT",
    "CHASSIS_MAX_LADEN_WEIGHT_Q";
SQL SELECT *
FROM Table1."V_GATE_PM_TRIP";

LOAD "TRIP_ID",
    "CREATE_DT",
    "MODIFIED_DT";
SQL SELECT *
FROM Table2."V_GATE_PM_TRIP_CNTR";

Thank you.

Upvotes: 0

Views: 1929

Answers (3)

dreTa
dreTa

Reputation: 27

In QlikView the LEFT JOIN work a little different that in SQL. In SQL you specify the fields from both table in the sentence of the left join:

SELECT *
FROM Table1 t1
     LEFT JOIN Table2 t2 on t1.FieldA=t2.FieldB

In QlikView the JOIN works with the field name, you don't need to specify the name. In the case the fields have different name, you have to rename one of them (or both) to get the same name:

TABLE1:
LOAD
    TRIP_ID1 as JOIN_KEY,
    PM_M,
    ARRIVE_DT
FROM Tabel1
;

LEFT JOIN (TABLE1)
LOAD
    TRIP_ID2 as JOIN_KEY,
    CREATED_DT,
    MODIFIED_DT
FROM Table2
;

Upvotes: 0

W.K
W.K

Reputation: 75

This is what i managed to do after looking at online and it generated the qvd with the data sorted by the date. 

TEMP_TABLE1:
LOAD "TRIP_ID",
    "PM_M",
    "ARRIVE_DT";
SQL SELECT *
FROM EODWADMIN."V_GATE_PM_TRIP";

LEFT JOIN 

LOAD "TRIP_ID",
    "CREATE_DT",
    "MODIFIED_DT";
SQL SELECT *
FROM EODWADMIN."V_GATE_PM_TRIP_CNTR";


TABLE2:
LOAD*, 1 AS FLAG
RESIDENT TEMP_TABLE1 ORDER BY ARRIVE_DT;

STORE TABLE2 INTO PM.QVD (QVD);

Upvotes: 0

EldadT
EldadT

Reputation: 932

Just add LEFT JOIN before the second load.
If you loading both tables from the same SQL source then just use the SQL syntax the same as you wrote in your question

Upvotes: 0

Related Questions