Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Merging Data from Multiple Tables

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.

5 Replies
Not applicable
Author

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

Join adds columns, concatenate adds rows.

Not applicable
Author

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

JJ

Not applicable
Author

Thank you Thomas.

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

Leonard Windham

IT Business Analyst

Not applicable
Author

you can do the join like this:

a:

load * from tablename;

join(a)

b:

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.

a:

load * from tablename;

concatenate

b:

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.

Not applicable
Author

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