topic Re: How to capture Insert , update ,delete in Qlik replicate in Qlik Replicate

How to capture Insert , update ,delete in Qlik replicate

AnujGupta — Fri, 13 Oct 2023 07:04:49 GMT

Hi,

I am looking to store insert , update & delete in a column called DELETED_FLAG =

CASE
WHEN operation_indicator("Y", "N", "N") = "Y" THEN 'Update'
WHEN operation_indicator("N", "Y", "N") = "Y" THEN 'Insert'
ELSE 'Delete'
END

In change processing, I have only Apply Changes Processing is ON. when I gave below expression, I am only seeing delete.

Re: How to capture Insert , update ,delete in Qlik replicate

john_wang — Fri, 13 Oct 2023 07:20:25 GMT

Hello @AnujGupta ,

If I read correctly, you want to store the operation in a dedicated flag column, let's call it "OPER". Then you may do it simply by add a new column with expression: operation_indicator('Delete','Update','Insert'), the sample:

The result in SQL Server:

Please ignore the NULL rows as they are Full Load rows (which I did not disable the Full Load).

Hope this helps.

Regards,

John.

Re: How to capture Insert , update ,delete in Qlik replicate

AnujGupta — Fri, 13 Oct 2023 07:32:42 GMT

Hi John,

I have tried the same below but only insert I can able to see.

where as delete and update also happening in source side.

Re: How to capture Insert , update ,delete in Qlik replicate

john_wang — Fri, 13 Oct 2023 07:36:57 GMT

Hi @AnujGupta ,

The previous samples based on MySQL Source and SQL Server target. do you mind to share the source/target endpoint types and what's the SQL you used to do insert/update/delete in source table? I'd like to confirm for you.

Regards,

John.

Re: How to capture Insert , update ,delete in Qlik replicate

AnujGupta — Fri, 13 Oct 2023 07:38:42 GMT

Source as a Oracle, destination as data bricks meta store table.

Re: How to capture Insert , update ,delete in Qlik replicate

john_wang — Sat, 14 Oct 2023 07:54:02 GMT

Hello @AnujGupta ,

I got challenge while I was provisioning Databricks environment today, I will work with IT team on that next week, Seems it's hard to confirm the behavior for you today. please work with Qlik support team (support case #00118262) on this issue and Support team would like to help you further.

Best Regards,

John.

Re: How to capture Insert , update ,delete in Qlik replicate

john_wang — Tue, 24 Oct 2023 15:40:03 GMT

Hi @AnujGupta ,

Looks to me the expression works fine in Databricks target task (Oracle is source):

operation_indicator('Delete','Update','Insert'), the task is FL + Store Changes. The result contains the 3 types of operations:

If you are still facing the issue, please upload the task Diagnostics Packages to support case.

Thanks,

John.