Resident load and left join combined

[swati_rastogi27]

Hi ,

I have a scenario in which I have to read data from database into a temporary table , and left join 3-4 tables and fetch resultset.

Below is the script :

LET vStartPeriod=Date(Date#($(vCurrDate), 'YYYYMMDD')-35, 'YYYYMMDD');

LET vEndPeriod=Date(Date#($(vCurrDate), 'YYYYMMDD')+7, 'YYYYMMDD');

TMP_TABLE:

SELECT DISTINCT COL1, TEST_DATE

                      FROM SCHEMA.TABLE1

                      WHERE COL1='ABC' AND TEST_DATE>=TO_DATE('$(vStartPeriod)','YYYYMMDD')

                      UNION

                      SELECT COL1, TEST_DATE

                      FROM SCHEMA.TABLE2

                      WHERE COL1='ABC' AND TEST_DATE>=TO_DATE('$(vStartPeriod)','YYYYMMDD')

                      UNION

                      SELECT COL1, TEST_DATE

                      FROM SCHEMA.TABLE3

                      WHERE COL1='ABC' AND TEST_DATE>=TO_DATE('$(vStartPeriod)','YYYYMMDD')//and

JOIN (TMP_TABLE)

LOAD   Date(MIN_DATE + IterNo() - 1)  as TEST_DATE

While MIN_DATE + IterNo() - 1 < Date(Date#($(vCurrDate), 'YYYYMMDD')+7, 'YYYYMMDD');

LOAD

MIN(TEST_DATE) AS MIN_DATE

resident TMP_TABLE;

Note : Above is a rolling view logic , to populate data for missing dates

Now, I need to join this rolled data to 4 other database tables and fetch the resultset.

Something like :

FINAL_TABLE:

NoConcatenate

LOAD

Date(Floor(TEST_DATE),'DD/MM/YYYY') As TEST_DATE,

TMP.COL1,

ONE.COL_ONE,

TWO.COL_TWO,

THREE.COL_THREE,

FOUR_COL_FOUR

Resident TMP_TABLE  TMP

LEFT JOIN SCHEMA.TABLE1  ONE ON TMP.COL1=ONE.COL1  AND TMP.TEST_DATE=ONE.TEST_DATE

LEFT JOIN SCHEMA.TABLE2  TWO ON TMP.COL1=TWO.COL1  AND TMP.TEST_DATE=TWO.TEST_DATE

LEFT JOIN SCHEMA.TABLE3  THREE ON TMP.COL1=THREE.COL1  AND TMP.TEST_DATE=THREE.TEST_DATE

LEFT JOIN SCHEMA.TABLE4  FOUR ON TMP.COL1=FOUR.COL1  AND TMP.TEST_DATE=FOUR.TEST_DATE

Is this achievable in QlikView?

[syukyo_zhu · Accepted solution]

Hi,

Yes you can do this. But don't mix the SQL command with Qlikview commande.

So:

You can try:

Tem_table:

SQL select

         ......

  where   ....;

left join (Tem_table)

load ...

resident Tem_table;

left join (Tem_table)

load ...

resident Tem_table;

Qlikview will create auotmaticly an join between your first table and your second table by the common fields.

hope helpful