Merge multiple rows into one

[satyender]

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

[SwathiPulagam · Accepted solution]

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

[SwathiPulagam]

Hi @satyender ,

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

Thanks,

Swathi

[Michael_Litz]

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

PREVIEW

[satyender]

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.

[satyender]

Thanks Michael for your response.

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

[SwathiPulagam]

Hi @satyender ,

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

Thanks,

Swathi

[satyender]

Source: DB2 i Series

Target: Snowflake

[SwathiPulagam]

Hi @satyender ,

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

Thanks,

Swathi

[john_wang · Accepted solution]

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.