Merging tables: concatenation

By Barry Harmsen

Merging tables using the concatenate and join prefixesOne of the subjects I often see new QlikView developers struggle with, especially those without prior database experience, is merging data from different tables. In this series of two posts I will explain how to use the CONCATENATE and JOIN prefixes to bring together data from multiple tables.

Simply put, the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

Read on for the first post, which covers the concatenation of tables in more detail.

Table of contents

Concatenation

Lets consider the following case; a paper company has recently decided to move the Sales Department of their Stamford branch office into their Scranton branch office. We have been asked to merge the employee data for both branches into a single Employee table.

We start out with the following source data:

Concatenate tables

  • The Scranton Employees table contains the names of the employees in the Scranton office, as well as the department they work in.
  • The Stamford Employees table contains the names of the employees in the Stamford office that are in the Sales Department.

If we do a straight load of this data into QlikView and look at the generated model, we will see that both tables are loaded separately and are joined via a $Syn table. This is not the desired result, as soon as we start adding more tables to this model it will gradually devolve into a spaghetti mess. The solution is to merge the two tables using one of the following methods:

Implicit concatenation

When two tables share the exact same fields, QlikView will assume that these tables belong together and will automatically add the records from the second table to the first. In our example, both tables share two common fields: Person ID and Name. However, as the Scranton Employees table contains an extra column, Department, that is not present in the Stamford Employees table, QlikView does not automatically concatenate the two tables.

As we know that all of the employees in the Stamford Employees table are in the Sales Department, the solution is to add a Department field to the Stamford Employees table that contains the value “Sales” for each employee. Once the field is added, QlikView automatically concatenates the two tables.

Preventing implicit concatenation

What if you have two tables with the same fields, but do not want them to be concatenated? In that case you can prefix your LOAD statement with the NOCONCATENATE prefix. This prefix tells QlikView to not concatenate the data but load it to a separate table.

NOCONCATENATE

Click here to download the example qvw file which shows implicit concatenation and the NOCONCATENATE prefix.

Explicit concatenation

While the implicit method works, I prefer using the explicit method of concatenation because it offers two advantages over the implicit version:

  • It is not based on assumption. Explicitly stating that two tables are being merged makes the script easier to read and maintain.
  • It allows concatenation of tables that do not share the exact same fields.

This method works by prefixing the LOAD statement with a CONCATENATE prefix. This prefix tells QlikView to append the data to the previously loaded table. Fields that do not match between the tables are automatically filled with null values.

By default, QlikView appends the data being loaded to the previously loaded table. To add data to another table, add the name of the target table in parentheses. I recommend always specifying the target table name, as it makes the script easier to understand and maintain.

In the script below you can see how the Stamford Employees table is concatenated with the Employees table, even though they do not share all of the same fields. Also note that the target table to append to is explicitly named after the CONCATENATE prefix.

Explicit concatenation

You can download the example qvw which demonstrates explicit concatenation by clicking here.

Downloads

Next time

This concludes the first post in this series on merging tables. In the next post I will cover the JOIN statement. In the mean time, feel free to drop your questions and comments in the comments section.

Update 2011/02/07: The second post in this series is online: click here.

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past few years I have been specializing in QlikView and a more user-centric form of BI. I have done numerous QlikView implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

21 Comments

  • 1
    Brian
    December 9, 2010 - 16:22 | Permalink

    Good job. I wish there had been such a simple explanation when I was first starting with QlikView.

  • 2
    Miguel Angel
    December 9, 2010 - 20:12 | Permalink

    Nice one. This is usually key to create fact tables that share some common fields that are key to analysis, specifically dates, and key as well in order to create a master calendar.

    Explicit concatenation with different field names actually create the fields that not exist in one table on the other with null values, so this is something to keep in mind when the amount of data is huge (depending on the hardware, of course).

    Greetings

  • 3
    paul yeo
    December 10, 2010 - 04:30 | Permalink

    Thank you for sharing . i like the example you use in your write up. Hope you can share on how to link sales table with inventory table or payment table.

  • 4
    Freddy
    January 8, 2011 - 14:26 | Permalink

    Interesting. As a new user I look forward to your next section. Thanks for sharing valuable tips!

  • 5
    Pontus A
    September 14, 2011 - 15:25 | Permalink

    Nice article! Having read about the powers that “explicit concatenation” hold, a curious question arise.

    When loading tables with similar names in a scipt one can implicitly concatenate several tables with one load-statement. One can do this by using ‘*’ or ‘?’ in the from-statement where the table names differ.

    Example: I have two xls-files named Table1 and Table2 which hold the same amount of fields with the exact same field names. To imlicitly concatenate these tables with one load-statment I can use this code:

    Tables:
    Load *
    From [Table*.xls];

    This will create a table named ‘Tables’ which is an implicit concatenation of ‘Table1’ and ‘Table2’. Using this technique to concatenate tables where the fields are not the same will lead to numerous of error messages but as long as the tables have the same fields this is a great “tool”.

    Question:
    I am curious if it is possible with some alteration of the code, to also use above showed technique to explicitly concatenating tables where the tables does not hold the same fields?

    Thanks for a good article and thanks to everyone making the time to read my post. Hoping for an answer.

    Best regards

  • 6
    Pontus A
    September 14, 2011 - 15:52 | Permalink

    Addition to my previous post!

    I made a misstake in the example code. Since the loaded files are .xls, one need to know the sheet-names in the xls-files and to specify this in the load-statement.

    The sheet-name in ‘Table1’ and ‘Table2’, where the tables are, is the same, ‘sheet1’. Hence the code should look like this:

    Tables:
    Load *
    From [Table*.xls]
    (biff, embedded labels, table is sheet1);

    I apologize for not getting this right the first time.

  • 7
    October 22, 2011 - 13:40 | Permalink

    How we can link two excel files in Qlik View program

  • 9
    Jon
    January 9, 2013 - 01:52 | Permalink

    Such good stuff. Thanks for the simple and easy to use explanation. Took me about 2min to apply this to the issue I was having!

  • 10
    Rene
    August 9, 2013 - 07:03 | Permalink

    I’m now going to remove all my Syncs. Thanks for the post.

    Groet,
    René

  • 11
    Artur
    April 16, 2014 - 13:23 | Permalink

    Hi Barry, good post and i have one question, please. In your example there is no overlap between those 2 lists of employees. What if Michael Scott is a manager in Scranton but he’s also listed on the Stamford employees list? Does it still make sense to concatenate two tables knowing that some key entries are present in both – or is it better to join rather than concatenate?

    rgds,
    Artur

    • 12
      Alexey
      September 5, 2014 - 17:59 | Permalink

      Artur, try use some temporary tables… steb by step.
      join 1, join 2

      and dont forget drop unuseless)

  • 13
    Jay
    August 7, 2015 - 10:34 | Permalink

    Hi all

    How to do this in the load script:

    table x:
    load table A
    outer join table B

    table Y:
    load table c
    outer join table D

    Concat table X,Y

    Basically I need to concatenate 2 joined tables from 2 sources
    Thanks

  • 14
    Erik
    November 11, 2015 - 14:38 | Permalink

    Thanks, very helpful!

  • 15
    Orson
    July 5, 2016 - 08:52 | Permalink

    Hi. Thanks for QV11 for developers and generally sharing your expertise – much appreciated.
    I’ve come across a reputable publication suggesting you can force concatenate tables with only a few common fields in both (which obviously defies the concatenation principle of at least most fields being common in the different tables). This particular publication refers to 2 fact tables. For arguments sake lets say only 30% of the fields are common to both tables, the other 70% not at all. Can you please explain the soundness of this idea. I’m not comfortable as I just see a lot of nulls where the fields are not common to both tables.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    QlikView blog round-up

    It's been a busy week with not much time to write, so instead of a fresh tip I bring you...

    Close