Sample ETL transformation with Qlikview tables

Here is a sample ETL transformation with Qlikview between tables

Temp:
LOAD DISTINCT PRODUCT_ID AS NEW_PROD_ID,
 PRODUCT_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
 PRODUCT_NAME,
 Date(LATEST_DATE, 'DD-MMM-YYYY') AS AS_OF_DATE,
 Date(LATEST_DATE, 'DD-MMM-YYYY') AS DET_AS_OF_DATE,
 C1,
 C2,
 C3
FROM
$(QVDPath)\TBL1.qvd (qvd)
WHERE Com_KEY_PRODUCT = 'Y' and num(Date(LATEST_DATE, 'DD-MMM-YYYY'))>=43082;

TempAsOfDate:
LOAD 
Date(LATEST_DATE, 'DD-MMM-YYYY') AS AS_OF_DATE
FROM
$(QVDPath)\TBL2.qvd (qvd)
where num(Date(LATEST_DATE, 'DD-MMM-YYYY'))>=43082;

INNER JOIN(Temp)
LOAD DISTINCT
 AS_OF_DATE
RESIDENT TempAsOfDate;

DROP TABLE TempAsOfDate;

TempHIST:
LOAD 
NEW_PROD_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
NEW_PROD_ID AS HIST_NEW_PROD_ID,
Date(LATEST_DATE, 'DD-MMM-YYYY') AS HIST_AS_OF_DATE,
1 AS TempFlag
FROM
$(QVDPath)\TBL3.qvd (qvd);

LEFT JOIN(Temp)
LOAD
DISTINCT
*
RESIDENT TempHIST;

DROP TABLE TempHIST;

NotStartedPRODUCTs:
LOAD DISTINCT
*
WHERE Flag = 1;
LOAD
*,
If(IsNull(TempFlag), 1, 0) AS Flag
RESIDENT Temp;

DROP TABLE Temp;

Temp1:
LOAD
 PROD_ID & '-' & Date(LATEST_DATE, 'DD-MMM-YYYY') AS Key,
 SAL,
 COMM,
 BUDGET
FROM $(QVDPath)\TBL4.qvd (qvd);

LEFT JOIN(NotStartedPRODUCTs)
LOAD
Key,
Sum(SAL) AS SAL,
Sum(COMM) AS COMM,
Sum(BUDGET) AS BUDGET
RESIDENT Temp1
GROUP BY Key;

DROP TABLE Temp1;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.