Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MEJ
New 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
Partner
Partner

Re: Best ways to join different files (keys)

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

10 Replies
Partner
Partner

Re: Best ways to join different files (keys)

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. 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
MEJ
New Contributor III

Re: Best ways to join different files (keys)

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

Partner
Partner

Re: Best ways to join different files (keys)

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;

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Partner
Partner

Re: Best ways to join different files (keys)

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

38936_Internal%20table%20view

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Tags (1)
MEJ
New Contributor III

Re: Best ways to join different files (keys)

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.

Partner
Partner

Re: Best ways to join different files (keys)

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
MEJ
New Contributor III

Re: Best ways to join different files (keys)

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

Partner
Partner

Re: Best ways to join different files (keys)

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?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
MEJ
New Contributor III

Re: Best ways to join different files (keys)

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.