EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials QlikView Tutorial QlikView Concatenate
 

QlikView Concatenate

Priya Pedamkar
Article byPriya Pedamkar

Updated June 21, 2023

QlikView Concatenate

 

 

Introduction to QlikView Concatenate

QlikView Concatenate function belongs to the family of ‘Join’ and ‘Keep’ command, though all three are used differently based on their nature. In a normal computer, the programming context Concatenate function is classified into a String/Text function, which joins strings together. This function is widely used in Excel, SQL, etc. In the context of QlikView, concatenate refers to the concatenation of rows where this function helps to append the rows from one table to another. We need to understand this difference before further studying the Concatenate function in QlikView.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How does the Concatenate Function work in QlikView?

The Concatenate function in QlikView is used to append the rows from one table to another. This command will function even if the number of fields in the two tables does not match or if the fields in the two tables are not identical. The concatenation takes place automatically if the tables have identical field names. When the field names are not identical, we force the Concatenate function to do the job.

Concatenate Function vs SQL Union Statement

It can be compared with the SQL Union Statement with some differences, as given below.

  • There should be the same number of columns in the select statement within the SQL union, whereas the number of columns in QlikView Concatenate can differ.
  • The Concatenate function could be used even if the tables do not have identical field names.
  • Identical records continue to remain in the table, unlike in the SQL Union.

Difference Between Concatenate and Concat()

The Concatenate and Concat() are both available in QlikView. Knowing the difference between their functions is good so we do not get confused later. The Concat() function is used to join strings/text/values/words together, whereas the Concatenate function is used to append rows from one table to another. Concatenate is used in the script, whereas the Concat() is used in expressions and rarely in the script.

Types of Table Concatenations in QlikView

Following are the types of QlikView Concatenate:

1. Automatic Concatenation

This is an automatic function in QlikView where the concatenation of two or more loaded tables will automatically occur when the number of fields and the names of the fields of the tables are similar. This will result in concatenating the different statements into a single table.

2. Forced Concatenation

This is a manual concatenation method that is done using the script. When the number of fields and the names of the fields differ between the loaded tables, we perform forced concatenation. Forced concatenation is performed by prefixing “Concatenate” in the script. This enables us to concatenate a table with another named table or the previously created table.

Code:

LOAD A1, B1, C1 from salesQ1.csv;
concatenate LOAD A1, C1 from salesQ2.csv;

The resulting internal table will have fields A1, B1, and C1. The number of records in this table will equal the sum of records from tables ‘salesQ1’ and ‘salesQ2’. As there is no field B1 in the table ‘salesQ2’, the value of records coming from ‘salesQ2’ from field B1 in the internal table will be NULL.

3. Preventing concatenation

Preventing concatenation is nothing but the prevention of automatic concatenation to take place. We have already seen that the concatenation of tables will automatically occur when the number of fields and the field names of the tables are similar. We can prevent this with the help of a noconcatenate. This will result in the loaded tables not getting concatenated even if the field names and the number of fields are the same.

Code:

LOAD A1, B1, C1 from salesQ1.csv;
noconcatenate LOAD A1, B1, C1 from salesQ2.csv;

Examples to Implement in QlikView Concatenate

The following are the examples to implement in the step by step manner:

Step 1: Let us open QlikView and then open the script editor (shortcut by pressing CTRL+E). We then need to load the input file. The input files could be loaded by choosing the ‘Table Files..’ option from the ‘Data from Files’ section. Browse for the input files and load them. In the example, we have loaded the files ‘Purchase.xlsx’ and ‘Sales.xlsx’; the script looks like below.

Sale Code:

LOAD S.No,
Market,
Month,
Sales
FROM
[C:\Users\smoham87\Desktop\Sales.xlsx] (ooxml, embedded labels);

Purchase Code:

LOAD S.No,
Market,
Month,
Purchases
FROM
[C:\Users\smoham87\Desktop\Purchase.xlsx] (ooxml, embedded labels, table is Sheet1);

Step 2:  Let us name the two tables before we concatenate them. We shall call the first table ‘Sales’ and the second ‘Purchases.’ The script will now look like the one below.

Sale Code:

LOAD S.No,
Market,
Month,
Sales
FROM
[C:\Users\smoham87\Desktop\Sales.xlsx] (ooxml, embedded labels);

Purchase Code:

LOAD S.No,
Market,
Month,
Purchases
FROM
[C:\Users\smoham87\Desktop\Purchase.xlsx] (ooxml, embedded labels, table is Sheet1);

Step 3: Now, we insert the ‘Concatenate’ script between the two load statements so that concatenation occurs. Finally, we run the script by clicking on ‘Reload’ and then click ‘OK.’ The final script will look like the one below.

Code:

LOAD S.No,
Market,
Month,
Sales
FROM
[C:\Users\smoham87\Desktop\Sales.xlsx] (ooxml, embedded labels);
Concatenate
LOAD S.No,
Market,
Month,
Purchases
FROM
[C:\Users\smoham87\Desktop\Purchase.xlsx] (ooxml, embedded labels, table is Sheet1);

Step 4: To visualize the concatenated table, let us create a Table Box. Right-click on the workspace, and under the ‘New Sheet Object’ option, select ‘Table Box’ to create a table. Next, we must add all the available fields and click OK. The output will be like the below table.

QlikView Concatenate 1

We can see in the above table that the two tables, ‘Sales’ and ‘Purchases’ have been appended into one table; the ‘Purchase’ and ‘Sales’ tables have been concatenated, and wherever the fields do not match, the records have been populated with Null values.

We can observe that the ‘Month’ and the ‘Market’ fields have duplicate values. The concatenation does not eliminate duplicate records.

Conclusion

By the end of this article, we should know how the Concatenate function works in QlikView. We also explored how Concatenate in QlikView compares to the SQL Union statement and how it differs. We have also studied the difference between string concatenation and table concatenation.

Recommended Articles

This is a guide to QlikView Concatenate. Here we discuss how it works, types, and examples to implement in QlikView Concatenate in detail. You can also go through our other related articles to learn more –

  1. AngularJS Services
  2. Pointers in Data Structure
  3. SELECT in MySQL
  4. Asymptotic Analysis
  5. Guide to QlikView Architecture

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW