Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Is SSN a key? If so comment out FIRST and LAST in second table and then join them.
Join adds columns, concatenate adds rows.
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
Thank you Thomas.
Appreciate the simple explanation on concatenate and join also. Never had it explained that easily before.
Leonard Windham
IT Business Analyst
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.
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