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, programming context Concatenate function is classified into a String/Text function, where it is used to join 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. It is important for us to understand this difference before we study further into 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. It is good to know the difference between their functions so that 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. The Concatenate is used in the script, whereas the Concat() is used in expressions as well as rarely used 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 the concatenation of the different statements into a single table.
2. Forced Concatenation
This is a manual concatenation method that is done using the script. Forced concatenation is done when the number of fields and the names of the fields differ between the loaded tables. Forced Concatenation is performed by prefixing “Concatenate” in the script. This enables us to concatenate a table with another named table or with the previously created table.
Code:
LOAD A1, B1, C1 from salesQ1.csv;
concatenate LOAD A1, C1 from salesQ2.csv;
Now the resulting internal table will have fields A1, B1 and C1. The number of records in this table will be equal to 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 had 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 option ‘Table Files..’ 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’ and 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 name the first table as ‘Sales’ and the second table as ‘Purchases’. The script will now look like below.
Sale Code:
LOAD S.No,
Market,
Month,
Sales
FROM
[C:\Users\smoham87\Desktop\Sales.xlsx]
(ooxml, embedded labels);
4.5 (3,717 ratings)
View Course
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 the concatenation takes place. Finally, we run the script by clicking on ‘Reload’ and then we click ‘OK’. The final script will look like 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 will have to add all the available fields and then click OK. The output will 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 have got an idea of how the Concatenate function works in QlikView. We also looked at how Concatenate in QlikView could be compared to SQL Union statement, and how it differs from it. 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 –
