topic Re: Updated date and inserted date in snowflake stage in Qlik Replicate

Updated date and inserted date in snowflake stage

kalesha786 — Tue, 28 Jun 2022 07:52:31 GMT

Hi Guys,

we have requirement like when we load into snowflake stage area from oracle. we need to add 3 more columns operation , inserted date, updated date.

We are doing CDC process , when record already exist we need to update and updated date should be update. But inserted date should not change .

have tried but inserted date also becoming null.

Can you please help me on this.

Thanks,

Kalesha Shaik.

Re: Updated date and inserted date in snowflake stage

SwathiPulagam — Tue, 28 Jun 2022 11:05:38 GMT

@kalesha786

You can use the below transformation on the table level \ Global level. The transformation column(inserted date) datatype must be VARCHAR(XX)

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
datetime('now')
ELSE
''
END

Also, you have to enable the internal parameter "emptyFieldAsNull" on the snowflake endpoint.

I will prepare an article and post it soon. Please try the above solution.

Thanks,

Swathi

Re: Updated date and inserted date in snowflake stage

kalesha786 — Tue, 28 Jun 2022 14:41:52 GMT

If possible can you please share exactly with screen shots , how to achieve

Re: Updated date and inserted date in snowflake stage

Michael_Litz — Tue, 28 Jun 2022 17:08:21 GMT

Hi,

While Swathi is correct , you will also need to add code that does a target lookup to retrieve the existing date when the operation is an update.

Please check this article in Qlik community that discusses this.

https://community.qlik.com/t5/Knowledge/Transformation-Target-Lookup-Insert-Date/ta-p/1805653

You can also check out this webinar discussing the same things.

https://community.qlik.com/t5/Knowledge/Customizing-Data-with-Qlik-Replicate/ta-p/1948509

Let me know if this helps out.

Thanks,
Michael Litz

Re: Updated date and inserted date in snowflake stage

kalesha786 — Tue, 28 Jun 2022 17:32:10 GMT

Thank you

Re: Updated date and inserted date in snowflake stage

SwathiPulagam — Wed, 29 Jun 2022 14:49:37 GMT

Hi @kalesha786 ,

Unfortunately, the Data Enrichment function like Target_lookup won't work for Snowflake target. You can follow the steps that I mentioned in the below article to fulfill your use case.

https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Transformation-column-INSERT-DATETIME-is/ta-p/1949728

Thanks,

Swathi

Re: Updated date and inserted date in snowflake stage

kalesha786 — Mon, 04 Jul 2022 15:35:09 GMT

@SwathiPulagam , have tried still not working

Re: Updated date and inserted date in snowflake stage

SwathiPulagam — Wed, 06 Jul 2022 20:22:22 GMT

Hi @kalesha786 ,

Please create a support case. I can have a working session with you to fix the issue.

Thanks,

Swathi

Re: Updated date and inserted date in snowflake stage

Heinvandenheuvel — Thu, 07 Jul 2022 02:17:09 GMT

Minor observation -

One may want to make use of $AR_H_TIMESTAMP instead of datetime('now'), because one probably needs to know when the insert happened, not when it was processed by Replicate.

This is with respect to the suggested

CASE WHEN $AR_H_OPERATION = 'INSERT'
then datetime('now') ELSE '' END

Hein.

Re: Updated date and inserted date in snowflake stage

nigupta — Tue, 24 Oct 2023 13:57:35 GMT

Any resolution on this topic ? I followed the recommendation but it is not working for me. During update I lose original insert_date. I am using PG as my source and Snowflake as Target.