Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jvk_sharma
Creator III
Creator III

How to join 5 tables carrying almost same data??

Hi,

I have 5 tables coming from excel carrying the data  each year like 2009.... to .2014.

These tables carry same columns but data is different according to the year.Can any one suggest me how to avoid synthetic key and I am wondering whether to join or concatenate, If possible pls explain with example.

Your help is appreciated.

Regards,

Sharma.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

In this sscenario its usually better to concatenate the files together. This will have effect of merging the rows in all spreadsheets into one spreadsheet with all the rows and no extra columns. It's as if you had manually added all the rows from each spreadsheet into 1 big spreadsheet first.

Joining would be done if the former operation would cacause duplication of records. You would join when you have extra fields in the 2nd table that you want to add onto the records from the 1st table. In this scenario you don't add rows,  just columns to the table.

View solution in original post

13 Replies
JonnyPoole
Employee
Employee

In this sscenario its usually better to concatenate the files together. This will have effect of merging the rows in all spreadsheets into one spreadsheet with all the rows and no extra columns. It's as if you had manually added all the rows from each spreadsheet into 1 big spreadsheet first.

Joining would be done if the former operation would cacause duplication of records. You would join when you have extra fields in the 2nd table that you want to add onto the records from the 1st table. In this scenario you don't add rows,  just columns to the table.

View solution in original post

jvk_sharma
Creator III
Creator III
Author

Ok Thanks Jonathan .Much appreciated.

So you reckon Concatenate is the best bet and how about synthetic keys or any perfomance issue ?

JonnyPoole
Employee
Employee

If you have other tables in the data model they will all Join to the resulting fact table on any field named the same. Use aliases to form the joins you want. In case of a need for a compsound join  you can use autonumber() function to build one.

Regular performance  optimization rules apply such as removing unneeded fields,  agaggregating unneeded uniqueness, (convert dates with timestamps to just dates) ...

jvk_sharma
Creator III
Creator III
Author

Thanks Jonathan. I have a similar document which has concatenated the tables and finally store into one qvd.

Indeed a quick reply.

Thanks.

jvk_sharma
Creator III
Creator III
Author

Hi Jonathan ,

Just a quick question I have just realised the data is changing not just year , it is changing every month from 2009 to 2014 ,If i am to concatenate the script will be too big and Any advice pls.

JonnyPoole
Employee
Employee

Stick with the same approach but do a for loop in the script to say, load every file in a given directory. I'm writing from a phone and can't do an effective search for you this moment but that would be the best way.

jvk_sharma
Creator III
Creator III
Author

Ok thanks for your efforts.

stevedark
Luminary Alumni
Luminary Alumni

Hi,

You may want to look at this blog post on dealing with a drop folder full of near identical files:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Hope you find it useful.

Steve

jvk_sharma
Creator III
Creator III
Author

Hi steve,

I need to set up  a config file using variables  by giving all the useful information like connection string and database name  or setting a target value (which will be changed by the users accordingly)etc which sits outside the qlikview and  these variables will pick up the changes automatically depending on the different environments we work like Dev,UAT and Prod.

Wondering if any one has a code how to write it.

Thank you,

Regards,

Sharma.