Solved: How to join 5 tables carrying almost same data?? - Qlik Community

Anonymous · ‎2014-10-14

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.

JonnyPoole · ‎2014-10-14

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

JonnyPoole · ‎2014-10-14

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.

Anonymous · ‎2014-10-14

Ok Thanks Jonathan .Much appreciated.

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

JonnyPoole · ‎2014-10-14

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) ...

Anonymous · ‎2014-10-14

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

Indeed a quick reply.

Thanks.

Anonymous · ‎2014-10-14

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 · ‎2014-10-14

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.

Anonymous · ‎2014-10-14

Ok thanks for your efforts.

stevedark · ‎2014-10-14

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

Anonymous · ‎2014-10-20

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.