QlikView - Concatenation


Concatenation feature in QlikView is used to append the rows from one table to another. It happens even when the tables have different number of columns. It differs from both Join and Keep command, as it does not merge the matching rows from two tables into one row.

Input Data

Let us consider the following two CSV data files, which are used as input for further illustrations. Please note the second data set has an additional column named Country.

SalesRegionOld.csv
ProductID,ProductCategory,Region,SaleAmount
1,Outdoor Recreation,Europe,4579
2,Clothing,Europe,4125
3,Costumes & Accessories,South Asia,6521
4,Athletics,South Asia,4125
5,Personal Care,Australia,5124
6,Arts & Entertainment,North AMerica,1245
7,Hardware,South America,456

SalesRegionNew.csv
ProductID,ProductCategory,Region,Country,SaleAmount
6,Arts & Entertainment,North AMerica,USA,1245
7,Hardware,South America,Brazil,456
8,Home & Garden,South America,Brazil,241
9,Food,South Asia,Singapore,1247
10,Home & Garden,South Asia,China,5462
11,Office Supplies,Australia,Australia,577

Load Script

We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. Then we edit the commands in the script to apply the concatenation between the tables.

concatenate_load_script

Next, we load the above data to QlikView's memory and create a Table Box by using the menu Layout → New Sheet Objects → Table Box where we choose all the available fields to be displayed as shown below.

concatenate_select_table

Concatenated Data

Completing above steps we get the Table box displayed as shown below. Please note the duplicate rows for the product ID 6 and 7. Concatenate does not eliminate the duplicates.

concatenated_table_chart
Advertisements