topic Re: Transformation in Qlik Replicate

Transformation

suvbin — Fri, 31 Mar 2023 02:45:38 GMT

Hi ,

Added a new column in transformation and used header column Ar_h _operation. If we done full load the values in target must be full_load_done and if values got inserted in the source , then in target it must be described as Inserted. i have tried like this.

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
"Inserted"
ELSE
"RR"
END

But everytime its doing insert. not sure how to include full load here. please let me know how to acheive this.

Re: Transformation

Steve_Nguyen — Wed, 29 Mar 2023 12:31:54 GMT

notes AR_H_OPERATION. For the initial full load, the value is INSERT, whereas for ongoing replication, it's either INSERT, UPDATE, or DELETE.

so not sure what you want from the AR_H_OPERATION on full load.

Re: Transformation

suvbin — Fri, 31 Mar 2023 02:48:15 GMT

My requirement is , If the task performs full load then the value in the target must be "full_load_done" and when the values got inserted in the source end , then in target it must be described as "Inserted".

Re: Transformation

Heinvandenheuvel — Fri, 31 Mar 2023 03:47:41 GMT

The full_load is NOT part of a transaction. You can distinguish a full-load from an application insert looking at variabes like AR_H_TRANSACTION_ID. Other variables may also be useful such as AR_H_USER.

The 'best' solution is Source Endpoint dependent. I encourage you to create a test task which moves ALL the $AR_H_ variables in added columns and compare the results for full-load vs application insert.

Hein.

Re: Transformation

Michael_Litz — Fri, 31 Mar 2023 20:30:22 GMT

Hi @suvbin

Please try this transformation as I believe it will do what you want:

CASE
WHEN $AR_H_STREAM_POSITION = '' AND $AR_H_OPERATION = 'INSERT'
THEN
"Full Load"
ELSE
"Inserted"
END

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

Michael

Re: Transformation

suvbin — Sat, 01 Apr 2023 08:19:41 GMT

Hi Michael,

Thank you for your response. when i used a full load task then the task stopped once the full load has been done.

But in the target end, the value was "inserted" instead of "full load". ..... not sure why.

And more over i think it will not take the second condition.

Re: Transformation

john_wang — Sat, 01 Apr 2023 08:56:12 GMT

Hi @suvbin ,

Thank you for your response. when i used a full load task then the task stopped once the full load has been done.

In general there are 2 possibilities: 1) it's Full Load ONLY task; 2) it's FL + CDC task however you enabled the STOPPING Task option (for example):

But in the target end, the value was "inserted" instead of "full load". ..... not sure why.

And more over i think it will not take the second condition.

@Michael_Litz 's expression works fine in my labs (Oracle to MySQL replication). What's your source and target DB types? and what's the CDC apply mode? You may open a support ticket with Diag Packages so far we may check further for you.

Regards,

John.

Re: Transformation

suvbin — Sat, 01 Apr 2023 10:43:00 GMT

hi @john_wang ,

it worked fine.

@Michael_Litz

It worked . Thank you.

Re: Transformation

john_wang — Sat, 01 Apr 2023 13:16:05 GMT

Glad to hear that, thanks @suvbin !