topic Re: one column inserted into source to multiple column in target. in Qlik Replicate

one column inserted into source to multiple column in target.

balasrinivasan — Sat, 24 Feb 2024 17:58:19 GMT

I have an oracle db as the source and I have an table named vm warranty approval table when a new row is inserted into the source table the target table must been inserted with 3 rows how to achieve it using Qlik replicate.

Re: one column inserted into source to multiple column in target.

john_wang — Sun, 25 Feb 2024 02:26:33 GMT

Hello @balasrinivasan ,

Welcome to Qlik Community forum and thanks for reaching out here!

I'm not sure what's the target side DB type however we need additional information to understand further about the request. While we are trying to replicate a single row from source Oracle to target side with duplicate 3 rows, the challenges are:

1- The table must have NO PK nor unique index otherwise we are facing PK/UI violation issue in target DB.

2- The triple Network/Storage/IO/CPU resources waste in the replication.

3- The design violates the database normal form (if target side is RDBMS).

I'd like to consider the special needs in consume apps side, or in target DB side straightly. Feel free to let us know if you need any additional information.

Best Regards,
John.

Re: one column inserted into source to multiple column in target.

john_wang — Sun, 25 Feb 2024 02:57:38 GMT

Hello @balasrinivasan ,

Seems you are mentioning 2 different scenarios :

1- In the article description "when a new row is inserted into the source table the target table must been inserted with 3 rows"

My previous comments focused on this scenario.

2- The subject is "one column inserted into source to multiple column in target"

This request is much easier to achieve in table level, or global level transformation. For example adding 3 additional columns (warranty_1/2/3) and their expression are duplicate - all the values come the same source column "NOTES":

Hope this helps.

John.

Re: one column inserted into source to multiple column in target.

SushilKumar — Mon, 26 Feb 2024 06:11:52 GMT

Hello Team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

Re: one column inserted into source to multiple column in target.

balasrinivasan — Mon, 26 Feb 2024 07:04:10 GMT

Can i acheive it by using composite keys , if so can you explain me the process of doing it , when one row is inserted into the source table the target table must be inserted with multiple rows based on some condition from the source table .

Re: one column inserted into source to multiple column in target.

john_wang — Mon, 26 Feb 2024 11:32:47 GMT

Hello @balasrinivasan ,

Yes, Qlik Replicate provides rich data processing functions, you may use source_lookup function to define computed columns based on conditions from the source tables, for example join different tables together.

Hope this helps.

John.

Re: one column inserted into source to multiple column in target.

Heinvandenheuvel — Mon, 26 Feb 2024 13:38:09 GMT

>> when one row is inserted into the source table the target table must be inserted with multiple rows

No can do. One source row insert can only cause on target row insert.

Replicate is not a programming /scripting language. It just moves rows from source to target with a bit optional filtering and column manupulations.

The 'other rows' will just have to be there on target already by full-load or as a consequence of those being update or insert on the source.

Without further clarification from of the desired functionality your best bet is TRIGGERS on the SOURCE which cause those multiple rows to be inserted (or have a benign update) on the source, perhaps in a temp/helper tables and have those events be moved by replicate to the proper target rows/tables.

Hein.