topic Re: Merge multiple rows into one in Qlik Replicate

Merge multiple rows into one

satyender — Tue, 01 Nov 2022 19:58:28 GMT

Hello Team,

I have a requirement of loading the tables from Source to Target. There are multiple rows in the target tables which I need to combine into one row for each table.

Could you please suggest how to do it in Qlik Replicate?

Thanks!

Satyender

Re: Merge multiple rows into one

SwathiPulagam — Tue, 01 Nov 2022 20:02:09 GMT

Hi @satyender ,

Qlik Replicate cannot merge all the records in a table as one record.

Just want to check what is your Source and Target databases.

Thanks,

Swathi

Re: Merge multiple rows into one

SwathiPulagam — Tue, 01 Nov 2022 20:04:05 GMT

Hi @satyender ,

I am just trying to understand your use case. Could you please elaborate more?

Thanks,

Swathi

Re: Merge multiple rows into one

Michael_Litz — Tue, 01 Nov 2022 20:10:30 GMT

Hi satyender,

If you are talking about having a row in the target that has fields from other tables on the source combined into a single row replicate can not do the merge, as per Swathi.

You may be able to do a limited workaround using source_lookup function which will allow you to add a field to a table and find the value of that added field by doing a lookup back into the source.

Please have a look at this article:
Transformation: Source Lookup - Oracle ROWID 5/4 ( Ted )
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

This is also documented in the user guide.

Thanks,
Michael

Re: Merge multiple rows into one

satyender — Tue, 01 Nov 2022 20:13:04 GMT

Thanks Swathi for your response.

This is the scenario.

There is a table in which there are multiple records in the COMMENTS column (say). I want to merge these into one. See the below pic for reference.

The data in the picture shows multiple rows as the comments spanned to multiple rows. Based on the line number priority 1,2,3,4,5 the merge should be done into one row.

Please let me know if you have any questions.

Re: Merge multiple rows into one

satyender — Tue, 01 Nov 2022 20:28:17 GMT

Thanks Michael for your response.

I have one table with multiple rows which need to be merged or combined into one.

Re: Merge multiple rows into one

SwathiPulagam — Tue, 01 Nov 2022 20:28:45 GMT

Hi @satyender ,

If my understanding is correct, do you want to merge the comments column based on PK values?

Thanks,

Swathi

Re: Merge multiple rows into one

satyender — Tue, 01 Nov 2022 20:28:58 GMT

Source: DB2 i Series

Target: Snowflake

Re: Merge multiple rows into one

SwathiPulagam — Tue, 01 Nov 2022 20:40:28 GMT

Hi @satyender ,

Please share your create table DDL for DB2 including Pk's.

Thanks,

Swathi

Re: Merge multiple rows into one

john_wang — Wed, 02 Nov 2022 04:25:55 GMT

Hello @satyender , copy @SwathiPulagam , @Michael_Litz ,

Please let us know you in what time want to merge the columns , during Full Load stage or CDC stage.

If it's in CDC stage, I think there is a bit ambiguous whatever the table has PK or not.

If you are meaning in Full Load stage, then the easiest way is creating a VIEW in DB400 and combine the columns by DB400 SQL statement(s), it's easy to control and maintenance than complex expressions in Replicate.

Or, you may ignore the merging/combination requirements in Replicate level but create a VIEW in target DB (Snowflake in your scenario) while your appls consume the data.

Hope this helps.

Regards,

John.

Re: Merge multiple rows into one

satyender — Wed, 02 Nov 2022 06:33:33 GMT

Hi @SwathiPulagam ,

Yes, I want to combine the multiple Comment rows into one.

Re: Merge multiple rows into one

satyender — Wed, 02 Nov 2022 06:34:45 GMT

Thanks @john_wang for your response.

I want this to be done at Full load stage in Qlik Replicate data load task.

Re: Merge multiple rows into one

SwathiPulagam — Wed, 02 Nov 2022 15:47:00 GMT

Hi @satyender,

As @john_wang suggested the easy way to do this is to create a VIEW in Source DB, the view must return one row based on your use case. You can set up a Full load task pointing to the view.

Thanks,

Swathi

Re: Merge multiple rows into one

satyender — Wed, 02 Nov 2022 16:50:16 GMT

Hi @SwathiPulagam , @john_wang

Unfortunately, we cannot do anything on the Source as we have only SELECT Permissions on the Source DB.

Re: Merge multiple rows into one

Heinvandenheuvel — Thu, 03 Nov 2022 12:38:18 GMT

Replicate can not do this.

A view on the source is probably the best solution as the data will be 'condenced'. Fewer rows, less redundant data.

>> we cannot do anything on the Source as we have only SELECT Permissions on the Source

If the project is valid, important enough, then someone can create a view for you. It's a one-time thing.

The Source DB layout was apparently good enough for its patrons, but understandably not desirable for the target DB users.

A view of the target is probably the next best solution.

I'd also look into treating the target table as an intermediate - helper table and run a daily or hourly transform to the real target table for the latest rows. There is possibly (probably?) more thinks to do to make the data better useable on the target. You may find that change tables are the preferred way to solve this although with those any update to a comment column would be rather tricky.

Hein

Re: Merge multiple rows into one

satyender — Sun, 06 Nov 2022 03:35:10 GMT

Thank you @Heinvandenheuvel for your response.

I have created Views on the target already as this is an extra step after loading the data I was looking for a solution while loading the data using Qlik Replicate. I guess I need to go with the Views on the target Snowflake and Clone them to the Database tables for now.

Thank you all for your responses.

Have a great weekend!

Re: Merge multiple rows into one

satyender — Wed, 09 Nov 2022 16:44:04 GMT

Hello Everyone,

I have replied earlier but somehow not seeing my responses here. It's weird.

Anyway, I am thinking to go with Views on Target.

Thanks everyone for your responses.