Concatenate

[Jennell_McIntire]

Concatenate is a prefix that can be used when loading a table in the script. Using concatenate explicitly states that you want the data that is currently being loaded to be appended to the end of a specified table. According to Qlik Help, the syntax looks like this:

Concatenate is often used when different sets of data, often from different data sources, need to be added to the same table such as a fact table. I often use concatenate when adding new data to a link table in my data model. This is an example of explicitly using concatenate to append rows to an existing table. If the data sets do not have the same data structure, the concatenate prefix must be used, otherwise a synthetic table will be created and the data sets will be store in separate tables. In the script below, the People table is loaded with two fields, Name and Title. The second data set, starting on line 8, is loaded using the concatenate prefix because the field Department does not already exist in the People table. By concatenating the table, the Department field will be added to the People table and the data in the second table will be appended to the end of the People table. The third table, starting on line 15, is implicitly added to the People table and does not require the concatenate prefix because it has the same three fields, Name, Title, and Department) as the new People table.

Below is the resulting table. Notice that the first two rows have null in the Department field because this data is from the initial data set that did not include the Department field.

Let’s look at another example of implicit concatenation. If a table is being loaded with the same fields as an existing table, the data will be appended to the end of the existing table even though the concatenate prefix is not used. For example, below in the script on the left, the data from the table starting on line 8 would be appended to the end of the People table because the second table has the same fields as the People table. This can happen even if these two data sets are loaded in different parts of the script. They do not need to be loaded sequentially. The script below, on the right, using the concatenate prefix will produce the same results explicitly.  I prefer to explicitly concatenate to avoid any confusion.

The resulting table will look like this:

In preparing this blog, I learned another way I could load multiple files with the same data structure taking advantage of implicit concatenation. The script below shows how I use the wildcard (*) to load several files with the same data structure.

What I learned is that I can also use a loop and implicit concatenation to do the same thing. After the script below runs, the TempData table will have all the data from the CSV files. The first time through the loop, the TempData table is created and subsequent times through the loop, the data is appended to the end of the now existing TempData table.

After 14 years with Qlik, I am still picking up new things. That is what makes my job so much fun!

Thanks,

Jennell