Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
satyender
Creator
Creator

Merge multiple rows into one

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

Labels (1)
3 Solutions

Accepted Solutions
SwathiPulagam
Support
Support

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

View solution in original post

john_wang
Support
Support

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.

 

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

View solution in original post

Heinvandenheuvel
Specialist III
Specialist III

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

 

View solution in original post

16 Replies
SwathiPulagam
Support
Support

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
Support
Support

Hi @satyender ,

 

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

 

Thanks,

Swathi

 

Michael_Litz
Support
Support

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
Creator
Creator
Author

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.

satyender_0-1667333398076.png

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
Creator
Creator
Author

Thanks Michael for your response.

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

 

SwathiPulagam
Support
Support

Hi @satyender ,

 

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

 

Thanks,

Swathi

satyender
Creator
Creator
Author

Source: DB2 i Series

Target: Snowflake

SwathiPulagam
Support
Support

Hi @satyender ,

 

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

 

Thanks,

Swathi

john_wang
Support
Support

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.

 

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