Connecting only certain fields in data model

[#1]

Hi everyone,

I have stored about 20 different qvd files that each contain a data from a table in my organization's database. I have created a new qvw file in order to connect these tables together. When I just load all of the tables into the data model, qlikview creates syn keys automatically that connect the tables; however, I am interested in doing this manually since there are some fields that I am not interested in connecting. Is there a way to do this, and if so what does the syntax look like?

Thank you.

[maxgro]

try to read here

Joins and Lookups

[#3]

Is there an equivalent of outer join, but keeping the tables separate?

[maxgro]

In Qlikview when jou join 2 tables in the script you get one table (see image)

If you want to stay with 2 tables and associate the 2 tables you must have some common (field name) field

Table1:

load

     field1 as field,

     field2

from

     ....;

Table2:

load

     fieldABC as field,

     fieldCDE

from

     ....

[#5]

The tables that I'm using happen to have several common fields, so qlikview creates synthetic keys - which I am trying to avoid. Would the only way to do this be to go back to the data import and only import fields so that each table is connected by only one field, or can I avoid synthetic keys while also keeping the tables separate using the data model script?

[#6]

You could create a table that consists only of the common fields, then drop the common fields from table 1 and table 2. The two tables would then be linked by this common table.

[maxgro]

synthetic keys?

Should We Stop Worrying and Love the Synthetic Key?

you can avoid syn key; one way (field1 2 3 --> syn key) is to modify script

instead of

load

     field1,

     field2,

     field3,

     .....

load

     field1 & '-' & field2 & '-' & field3 as mynewjoinfield

     ....

[#8]

Maybe I'm thinking of this in the wrong way. I have one table that has all of the fields that I am interested in using for connections (plus a few more). So essentially, by connecting all of the other 19 tables to this one table by one or more of these "connection" fields, my data model will be complete. Am I correct in saying that the only way that I can accomplish this by re-naming the matching fields that I am not interested in connecting so that qlikview only connects the ones that I am interested in connecting?

[ToniKautto]

The way the tables should be associated with each other by Qlikview, depends on how the content logically relates. I would say it sounds a bit much with 19 dimension or attribute tables linked to one fact table.

Why not join some of the attribute tables into your fact table? A join can also be done over several fields, so if you have two common field between two tables, the join will be made by using these as key fields.

Generally it is advisable to work with optimizing the data model, and not just load it directly as is from a data source. Often there are also things that you want to pre-calculate in the script, to make the application front-end perform in best possible way and minimize runtime calculations.