Resident load and left join combined

swati_rastogi27 — Thu, 21 Apr 2016 07:05:05 GMT

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?

Re: Resident load and left join combined

syukyo_zhu — Thu, 21 Apr 2016 15:16:50 GMT

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

topic Re: Resident load and left join combined in QlikView

Resident load and left join combined

Re: Resident load and left join combined