Update Modified Date Field while Keeping Create Date Field

[#1]

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!

[sfatoux72]

‌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;

[maxgro · Accepted solution]

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;

[#4]

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

[#5]

This works great, thank you!