Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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).
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;
I think this blogpost by @hic could be a good read for you Qlik Design Blog Synthetic 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.
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".
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...
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?
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.