Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MEJ
Contributor III
Contributor III

Best ways to join different files (keys)

Is there a best practice how to join different tables?

I normally try to use left join with my main table, which creates only one list of records (see Capture1.JPG), as opposed to a whole bunch of table structures with links with each joined key (see Capture.JPG.)

I want to be able to export the raw database as it is, not with any outer joins, creating new records that are not part of the data base, just because they are part of a mapping table.

Is there a preference how to do it? 

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Yes, I would recommend you to consider it, you might find benefits doing so. Sometimes it gets really hard to do correct calculations on joined tables where you have one-to-many relationships.

View solution in original post

10 Replies
Vegar
MVP
MVP

You don't neeed to join all your data into a single main table, there are multiple scenarios where a join will make trouble to your app development. 

A good practice in most scenarios is to aim for  a star scheme data model. It is a good practice both memory wise and for user understanding  of the data.

 

You can read Qliks help page for best practices for data modelling. 

 

MEJ
Contributor III
Contributor III
Author

Thanks for your input.

I think I have noticed that in some instances, a synthetic key is created when not using a "left join", which is why I have tried to keep to that standard instead of creating a star scheme. I agree, it is far easier to understand how tables are connected if I don't use left join. I will try to save a copy of my application and reload it without the joins, and see if it yields the same result.  

I also noticed that in some cases it is not possible to visualize the frequency in the list boxes as it has created a key (synthetic).

Vegar
MVP
MVP

Synthetic keys are generated when you have more than one common field that associating records between two tables.  

In your picture you have two fields, start and end that are linking between your intervalmatch table and another table that where cropped out of the picture. As long you know the link is correct (using two fields) there is nothing technincal wrong with a synthetic key. To avoid them you could create a composite key.

Load 
Keyfield1  & '|' & Keyfield2 as %key,
Key1,
Key2,
Etc
From Table 1;

 

Load 
Keyfield1  & '|' & Keyfield2 as %key,
//Key1,
//Key2,
Etc
From Table 1;

Vegar
MVP
MVP

I think this blogpost by @hic  could be a good read for you Qlik Design Blog Synthetic Keys 

38936_Internal%20table%20view

MEJ
Contributor III
Contributor III
Author

Actually the picture was not from my own application. Just an example from Internet to show a star scheme (although this star scheme happened to have created synthetic keys). 

I always try to create my QV apps with no synthetic keys.

However, I tried to reload my application without any joins (started with just one of the mapping tables for a start). Immediately the script stopped when reaching that part of the script. Probably due to the output file (QVD) was created with other logic or format?

The problem is that I use Left join into my main table (probably 10+ mapping tables), from which I create a QVD file. As I use Left Join, I get access to all mapping tables' (left join tables) fields as well, and by omitting the "Left Join", those fields are no longer available when I store the file as QVD. I'm sure there is a workaround, but as the script looks now, it is not.

Vegar
MVP
MVP

What if you store the main table into main.qvd and the dimension into a dimension.qvd?

A comment: I assume that when you write "mapping table" you are reffering to your left join table. In QlikView and Qlik Sense "mapping tables" and "left join tables" are two different things. You will confuse experienced Qlik developers by using the word "Mapping".

Script prefixes - Mapping

Mapping functions

 

MEJ
Contributor III
Contributor III
Author

Sorry for the misuse of acronyms. It should of course be a left join table in that case (stored in an Excel file. E.g. a country code being the unique key and using that table to derive Country name, etc.).

Not quite sure how I should go about storing all the different tables to be used to read from in a "single line" table.

By using Left Join, then I will have an easy to understand table, with unique rows. 

Hmm...

Vegar
MVP
MVP

Do you need to have a single qvd as output? Can't you transform into multiple qvds and in your final application load data from multiple qvds?

MEJ
Contributor III
Contributor III
Author

I guess it is a lot easier to have just one qvd file that is exactly in the right format. It's probably possible to store the other tables (at least 10 of them), and then load them all. I might give it a go to see if it is possible, but I suspect it will be messier.