Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

Have you ever loaded two or more tables in your script to find that when the script was complete your data model had only one table?  I ran into this issue when I first starting scripting and I think this is probably a common scenario of new Qlik Sense and QlikView developers.  Qlik Sense and QlikView will automatically concatenate two tables if they have the same field names.  By using the NoConcatenate prefix, you can prevent this from happening.  Let’s look at an example.  When I reload the script below, I end up with one table named Table1 with records from both data sources – Sheet1 and Sheet2.

Script1.png

First Table1 is loaded.  When Table2 is loaded with the same fields, Qlik Sense automatically concatenated the data from Sheet2 to Table1.  If I had used the NoConcatenate prefix as seen below, I would end up with 3 tables in my data model: Table1, Table2 and $Syn 1 Table (a synthetic table).

Script2.png

Data Model Viewer after script above reloads:

TableViewer1.png

The synthetic table was created because my data model had two tables with the same field names so Qlik Sense created a synthetic key to handle this.  You can read more about synthetic keys here.  You should avoid synthetic tables when you can.  There are various ways you can create two tables without creating a synthetic table.  One is to use the Qualify statement – this will add the table name as a prefix to the field name.  Another way, which is more popular, is to rename the fields in the second table when you load them.  These two options will produce separate tables and no synthetic tables.

 

Now you may be wondering why you need to use NoConcatenate at all.  I often use it when I want to perform some additional manipulation to the data in an existing table.  For example, in the script snippet below, I am loading life expectancy data for various countries, genders and years.  Since I am using the CrossTable statement in the first Load statement, I cannot add a where clause to load only the 2014 data.  So, in the second Load statement, I perform a NoConcatenate Load to load all the data from DataTemp2 where year is 2014.  Then I delete DataTemp2 since I have the data I need in the DataTemp table.  The most important part of this script is the Drop Table at the end.  This deletes the first table I loaded (DataTemp2), preventing a synthetic table from being loaded.

Script3.png

This is often the way I use NoConcatenate - to create a new table from an existing table with some modifications and then I drop the original table.  NoConcatenate is useful and once you get the hang of it, you will find that NoConcatenate is a helpful prefix you will use often in your script.

 

Thanks,

Jennell

2 Comments