Updated June 21, 2023
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.
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.
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 –
