topic Merging Data from Multiple Tables in QlikView

Merging Data from Multiple Tables

Thu, 17 Nov 2011 14:01:23 GMT

How to merge data from multiple Excel spreadsheets using QlikView.

I'm sure it can also be done within Excel -- thought I would use QlikView to increase familiarity with the tool's features.

I have 2 Excel tables.

Table 1 has fields: SSN FIRST LAST CITY STATE

Table 2 has fields SSN FIRST LAST SALARY HIRE DATE

I would like to combine, append, join (which ever term is accurate) the SALARY and HIRE DATE fields to the first Table.

Ideas and best practices please? Thank you.

Proper way to load the data

Advisable ways to display (table box, multibox, list box, etc)

Thank you.

Merging Data from Multiple Tables

Thu, 17 Nov 2011 20:36:39 GMT

Is SSN a key? If so comment out FIRST and LAST in second table and then join them.

Join adds columns, concatenate adds rows.

Re: Merging Data from Multiple Tables

Thu, 17 Nov 2011 21:02:36 GMT

If you want to merge 2 tables use a join condittion.

Look at the attachment.

The merge is done thanks your common field. In your case SSN, FIRST, LAST

So the syntax looks like

Table:

Load ...

From excelFile1 ;

Join(Table)

Load ...

From excelFile2 ;

The second part of your question depends of what you want to do.

If you want to export the combine file in excel, go with a table box.

List Box is used to make some filters in a chart

Re: Merging Data from Multiple Tables

Thu, 17 Nov 2011 21:03:00 GMT

Thank you Thomas.

Appreciate the simple explanation on concatenate and join also. Never had it explained that easily before.

Leonard Windham

IT Business Analyst

Merging Data from Multiple Tables

Fri, 18 Nov 2011 06:15:32 GMT

you can do the join like this:

load * from tablename;

join(a)

load * from tablename;

> it will generate one table i.e a with the data i.e common to both the tables in addition to records that is not common.

load * from tablename;

concatenate

load * from tablename;

>it will append the records of b in table a with null values.

note: qlikview join is outer join unless specified.

hope this makes you understand the concept of join and concatenate.

Re: Merging Data from Multiple Tables

Thu, 03 Jan 2013 10:02:13 GMT

Hi alwindham,

I would say, if most of the fields are same between the tables use concatenate.

But, make sure you use the keyword Concatenate between the tables in the script.Or else it will result in creating

synthetic key and you will end up having three tables in the data model.

In your case, also identify the primary(unique) key between the tables.

Hope this helps,

Regards,

Snehal Nabar