Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
Thanks,
Swathi
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
If possible can you please share exactly with screen shots , how to achieve
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
Thank you
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.
Thanks,
Swathi
@SwathiPulagam , have tried still not working
Hi @kalesha786 ,
Please create a support case. I can have a working session with you to fix the issue.
Thanks,
Swathi
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.
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.