Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to be able to update fields in a stored QVD file while retaining the original Created_Date field. The 'Key' would be a combination of both Product_ID and Company_ID, since multiple companies can share the same product type. Please see the below examples of what I'd like to do:
QVD Table:
PRODUCT_ID | COMPANY_ID | CREATED_DATE | MODIFIED_DATE |
---|---|---|---|
01 | 100 | 1/1/2016 | 4/1/2016 |
02 | 100 | 2/1/2016 | 4/1/2016 |
03 | 200 | 4/1/2016 | 4/1/2016 |
New Table:
PRODUCT_ID | COMPANY_ID | MODIFIED_DATE |
---|---|---|
01 | 100 | 6/1/2016 |
03 | 200 | 6/1/2016 |
03 | 300 | 6/1/2016 |
Desired Result:
PRODUCT_ID | COMPANY_ID | CREATED_DATE | MODIFIED_DATE |
---|---|---|---|
01 | 100 | 1/1/2016 | 6/1/2016 |
02 | 100 | 2/1/2016 | 4/1/2016 |
03 | 200 | 4/1/2016 | 6/1/2016 |
03 | 300 | 6/1/2016 | 6/1/2016 |
I have tried to start this process with an incremental load (inserting and updating fields), but am not able to retain the Created_Date field. Do you have any suggestions?
Thanks in Advance!
Tmp:
LOAD PRODUCT_ID,
COMPANY_ID,
MODIFIED_DATE as M2,
PRODUCT_ID & '|' & COMPANY_ID as key
FROM
[https://community.qlik.com/thread/235704] (html, codepage is 1252, embedded labels, table is @2);
JOIN (Tmp)
LOAD PRODUCT_ID,
COMPANY_ID,
MODIFIED_DATE as M1,
CREATED_DATE as C1
FROM
[https://community.qlik.com/thread/235704] (html, codepage is 1252, embedded labels, table is @1);
Final:
load PRODUCT_ID,
COMPANY_ID,
IF(len(trim(M2)), M2, M1) as MODIFIED_DATE,
IF(len(trim(C1)), C1, M2) as CREATED_DATE
Resident Tmp;
DROP Table Tmp;
Try somme thing Luke this:
Data:
Load PRODUCT_ID, COMPANY_ID, CREATED_DATE, MODIFIED_DATE as MODIFIED_DATE_OLD
from QVD_Table ;
left join (Data)
Load PRODUCT_ID, COMPANY_ID, MODIFIED_DATE as MODIFIED_DATE_NEW
from NEW_Table ;
left join (Data)
Load PRODUCT_ID, COMPANY_ID, If(isnull(MODIFIED_DATE_NEW), MODIFIED_DATE_OLD, MODIFIED_DATE_NEW) as MODIFIED_DATE
Resident Data ;
drop field MODIFIED_DATE_OLD;
drop field MODIFIED_DATE_NEW;
Tmp:
LOAD PRODUCT_ID,
COMPANY_ID,
MODIFIED_DATE as M2,
PRODUCT_ID & '|' & COMPANY_ID as key
FROM
[https://community.qlik.com/thread/235704] (html, codepage is 1252, embedded labels, table is @2);
JOIN (Tmp)
LOAD PRODUCT_ID,
COMPANY_ID,
MODIFIED_DATE as M1,
CREATED_DATE as C1
FROM
[https://community.qlik.com/thread/235704] (html, codepage is 1252, embedded labels, table is @1);
Final:
load PRODUCT_ID,
COMPANY_ID,
IF(len(trim(M2)), M2, M1) as MODIFIED_DATE,
IF(len(trim(C1)), C1, M2) as CREATED_DATE
Resident Tmp;
DROP Table Tmp;
Unfortunately this didn't work - Created Date is not populated for new products. It also doesn't take into account Company_ID and Product_ID
This works great, thank you!