Home  >  Blog  >   QlikView

Concatenating Two Tables in QlikView

Rating: 5

Table Combining and Concatenation

Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows of the two input tables. CONCATENATE prefix adds rows to a previously loaded table. The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are certain differences between them.

In this post, we have emphasized the importance of developing the data model in the star schema format (if possible) and avoiding synthetic tables and circular references. Instead of using link tables, using key tables, and learning some other renaming strategies discussed in this book, one of the best ways to avoid these issues is to combine tables where it makes sense.

If you would like to become QlikView Certified professional, then visit Mindmajix - A Global online training platform: "QlikView Certification Training Course". This course will help you to achieve excellence in this domain.

As a note of clarification, the word concatenate in qlik view-speak describes adding the table’s rows onto another table. A qlik view join, however, is best described as appending a table’s column onto another table. Most of the time, developers will need to tell qlik view explicitly when to concatenate and join.

Because QlikView is associative, there are times when the software automatically concatenates data fields from two or more separately loaded tables. This automatic concatenation happens when the number and names of the table column are exactly the same. Qlik view will automatically concatenate one statement with another, as the following statements illustrate:

Load product-name, product-ID,
Category from toys. Csv;
Load product-ID, product-name,
Category from electronics.csv;

These two statements are essentially treated as one since both the tables have identical columns (data fields) and the number of columns is generally three. All the toy and electronic products are combined into one table-the first listed table. The electronics table will not appear in the data model.

MindMajix YouTube Channel

Related Article: QlikView Interview Questions 

If you want to prevent this automatic concatenation, you must rename fields or use the no concatenate statement. This will prevent the automatic concatenation of the tables even if they have identical names and numbers of data fields. The following code is an example of the no concatenate statement:

Load product-ID, product-name,
Category from TOYS.csv;
No concatenate load product-ID,
Product-name, category

In most cases, qlik view developers will need to layout instructions for qlik view to follow concatenation performance. Qlik view calls this forced concatenation, and it is necessary when the tables that have to be combined do not have the same number or names of columns.

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers!

A forced concatenation is done using the concatenate prefix (before load) in the script. This will concatenate the table following the concatenate statement to the table created immediately before this statement. Here’s an example of a forced concatenation:

Load product-ID, product-name,
Category from TOYS.csv;
Concatenate load product-ID,
Category from electronics. Csv;

Because we did not specify the table name to concatenate it to in the concatenate statement (concatenate table name is the best practice), the statement appends the rows from electronics. Csv onto the last table created (TOYS). The resulting internal TOYS table has the product-ID, product-name, and category fields. The number of records in the resulting table is the sum of the number of records in TOYS.csv and electronics.csv. The value of product-name in the records coming from electronics.csv is null.

Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
QlikView TrainingMar 02 to Mar 17View Details
QlikView TrainingMar 05 to Mar 20View Details
QlikView TrainingMar 09 to Mar 24View Details
QlikView TrainingMar 12 to Mar 27View Details
Last updated: 03 Apr 2023
About Author

Vinod Kasipuri is a seasoned expert in data analytics, holding a master's degree in the field. With a passion for sharing knowledge, he leverages his extensive expertise to craft enlightening articles. Vinod's insightful writings empower readers to delve into the world of data analytics, demystifying complex concepts and offering valuable insights. Through his articles, he invites users to embark on a journey of discovery, equipping them with the skills and knowledge to excel in the realm of data analysis. Reach Vinod at LinkedIn.

read more
Recommended Courses

1 / 15