Tables in the Qlikview data cloud are linked to other tables by a maximum of only 1 field. If more fields have the same name, a synthetic key/table is created. There are several solutions to this, but what I am looking for is some background on why Qlikview can only link on 1 field, as almost every datamodel links tables on more that one key (compound key).
I'm new to QlikView, so I don't know if I'm correct...but I think QlikView doesn't analyze data that we load in...it just creates tables based on our LOADS....were if two fields with same name are considered same and not differentiated....
Basically it cannot understand that in practice most tables are linked using a compound key....
What I usually do is create compound keys in my script, sometime consisting out of a number of fields. I make sure that the keys exist in both the tables I would like to join.
what Qlikview does is to link the fields in Different tables with samename as commonfield. Now if you wanted to create a compound Key for two fields you can write the syntax in such a way that the compound key is created. If you dont want to create compound Key just name the fields same in the two tables. It will result in a synthetic Key. But this procedure is not recommended, when lot of Synthetic Keys are present in the Datamodel as it results into 1:n connections and the data is not correct.
Qlikview follows Natural Join and Synthetic Key is not same as compound key.
If you have more than one common columns in tables , qlikview will make synthetic key. If you are doing alias or qualify but you want link based on more than one common column then make compound key.
ie (say)
autonumber( A & '-' & B & '-' & C) as compoundkey.
in qlikview autonumber ( A & B & C) is not same as autonumber ( B & C & A)
but
in case of synthetic key order of columns has no effect.
We always create compound keys using autonumber to make sure the keys are correctly taken.
The naming in the tables must be unique per table:
e.g.
table1:
LOAD columnA, columnB, columnX, columnY ... FROM table1;
table2:
LOAD columnA, columnB, colulmnQ, columnR ... FROM table2;
becomes something like:
table1:
LOAD *
, autonumber(table1.columnA+table1.columnB) as compoundkey
;
LOAD columnA as table1.columnA, columnB as table1.columnB ... ;
table2:
LOAD *
, autonumber(table2.columnA+table2.columnB) as compoundkey
;
LOAD columnA as table2.columnA, columnB as table2.columnB ... ;
Always keep in mind that fields with the same name are automatically connected to eachother as key ... if more than one field is connected between 2 tables a syntetic key is created ... then it's better to create compound keys and rename the fields to a unique name.
The generation of autonumber field is not guaranteed to give the same result when first building qvd files and load applications from these files. We have had a scenario in which the auto generated customer number was different between the orders and customers file. This was the result of building the qvd's in multiple scripts.
This is turned out to be a no-go when using this in combination with storing order data in qvd files per year. No way to be 100% sure that the generated number was similar all the time (perhaps this has changed in QV 10 - haven't tried this yet.)
With large amounts of data, they say keys based on numbers (created with autonumber) search a lot quicker than regular keys.
We don't have that big amount of data to really notice the difference ... but code-technical we have a neater way of connecting and we have a much better overview of how the data relates and in one overview we exactly know which are the keys (we use the '%' character to indicate the key).
