I need to load 2 tables in my load script and I need the tables to be joined on cust_no and txn_date. Initially when I loaded them it created a synthetic table with columns $Syn1, cust_no and txn_date. Is this an issue? Everyone says that this is not best practice.
That being said, if it's not best practice, can someone suggest an alternative? Would creating a concatenated key (cust_no & txn_date) be a better approach? If yes, could someone provide either some guidance or a link to where I can find out more? I'm not seeing anything in the QV reference manual.
For years, synthetic keys were considered a "bad practice", and many developers (myself included) still prefer to avoid them. However, there have been recent discussions that synthetic keys of this kinds (just a couple of fields, nothing too crazy) might be OK, and this thinking was officially confirmed by QlikTech. SO, if you don't experience any problems (like, for example, unusually slow load times), - you can probably live with the synthetic key.
Techniques to avoid synthetic keys might not be described in the reference. They are taught as a part of "Developer II" class. Two most common techniques are called "Link Tables" and "Concatenated Tables". There are numerous descriptions of those two in this forum - please search for "link table" and you'll find plenty.
For years, synthetic keys were considered a "bad practice", and many developers (myself included) still prefer to avoid them. However, there have been recent discussions that synthetic keys of this kinds (just a couple of fields, nothing too crazy) might be OK, and this thinking was officially confirmed by QlikTech. SO, if you don't experience any problems (like, for example, unusually slow load times), - you can probably live with the synthetic key.
Techniques to avoid synthetic keys might not be described in the reference. They are taught as a part of "Developer II" class. Two most common techniques are called "Link Tables" and "Concatenated Tables". There are numerous descriptions of those two in this forum - please search for "link table" and you'll find plenty.
concatenating the two keys, with or without autonumber(), won't solve the problem by itself (in most cases). You really need to follow the technique of building the Link Table, in order to receive proper results.
Hi, i agree with Oleg, i don´t think that the synthetic tables are a bad practice...sometimes is very difficult to wipe them from the model, and they don't affect it. They bother me only when i have very large data loads, sometime make the model very slow. Anyway, you can avoid them using a concatenated key as Pat said or don't cancatenate...use AutonumberHash withe the two fields separated...like:
AutonumberHash128('KEY',cust_no,txn_date) as KEY,
I don´t know if it's a good way or the best practice...but i've tables in my models with more than 20 million records and autonumbers with five fields, and it works fine.
Thanks to everyone for responding. For now I'n going to go with creating a link table as I remember reading about this a while back and it seems to make the most sense to me. Thanks again!
I like to avoid synthetic keys not because of performance, but because it makes the data model (CTRL + T) look much cleaner. Moreover, it also provides you with a way to display all the rows in a table without the elimination of duplicates.
