Automatic Concatenation Watch-Out

This is a mistake I make every once in a while and it always puzzles me for a few minutes before I figure out what happened. In the loadscript, if you load data from several sources and use exactly the same field names then QlikView will automatically concatenate the data from the second source onto the table created for the first source. It does this unless you specify the keyword NOCONCATENATE.

Here's an example: Imagine that your loadscript loads data from a customer master table and following that you write two load statements to create a small table of just the Club customers and a second table of just the Military customers. Maybe you intend to use the small tables in a Where Exists statement further down in the script. You might write the loadscript statements like this:

CLUB_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'CLUB';
MILITARY_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

That's not going to work the way you intended. There's no error or warning but after the loading you'll discover that there is no MILITARY_CUSTOMERS table. What happened is that QlikView concatenated the customer_no values from the second load statement onto the CLUB_CUSTOMERS table. It did that because the field names in the second table are exactly the same as the field names from the first table. It will do this even if the two load statements are not one after the other like in my example but even if there are other loadscript statements in between. The automatic concatenation feature is probably useful in quickly assembled loadscripts but it can cause trouble for more complex applications. You could change the field name in the MILITARY_CUSTOMERS table to be different, like customer_no_military and the automatic concatenation won't happen. OR, and this is my preference, add the NOCONCATENATE keyword to the second load statement like this:

MILITARY_CUSTOMERS:
Noconcatenate Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

Then you will end up with the two small tables that you intended.

Another keyword, CONCATENATE, is sometimes useful when you want to load two sets of data where the fields are not exactly the same into the same table. When you use this one though it will concatenate the data being loaded to the previous table in the loadscript.