topic Re: Update Modified Date Field while Keeping Create Date Field in QlikView

Update Modified Date Field while Keeping Create Date Field

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Update Modified Date Field while Keeping Create Date Field

sfatoux72 — Fri, 07 Oct 2016 23:38:13 GMT

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

Re: Update Modified Date Field while Keeping Create Date Field

maxgro — Sat, 08 Oct 2016 18:01:07 GMT

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;

Re: Update Modified Date Field while Keeping Create Date Field

Sun, 09 Oct 2016 04:03:15 GMT

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

Re: Update Modified Date Field while Keeping Create Date Field

Sun, 09 Oct 2016 04:10:17 GMT

This works great, thank you!