What do you do when you need to associate multiple fields between two tables? Qlik creates a synthetic key and everyone says that’s bad! First it’s good to understand what a synthetic key is. In reality it is basically just a concatenated key generated by Qlik’s Associative Engine. The reason it’s taboo is because it’s automatic. You don’t in theory know how it was made or what the results are going to be because it happened in a black box.
What you should do is create your own concatenated key. As always, it is best practice to be explicit. So even if the output is the same, it’s better to program with thoughtfulness, rather than laziness.
Data: Load Region&'|'&ProductId as %Region_Product, Region, Qty Inline [ Region, ProductId, Qty North, A, 12 East, B, 7 West, C, 19 South, A, 11 North, B, 5 East, C, 8 West, D, 10 South, C, 3 ];
Product: Load Region&'|'&ProductId as %Region_Product, ProductId, Price Inline [ Region, ProductId, Price North, A, 5 South, A, 7 East, A, 5 West, A, 6 North, B, 13 South, B, 15 East, B, 8 West, B, 9 North, C, 1 South, C, 1 East, C, 1 West, C, 1 North, D, 21 South, D, 15 East, D, 12 West, D, 7 ];
*I almost always use a pipe “|” as the separator.
By doing the above, it will remove the synthetic key.
In this case, the data results are the same. However, in complex data models synthetic keys can produce unexpected results. Also, in some scenarios you may not even want the fields associating. So the answer would not be a concatenated key at all. You would either want to remove the field or rename it, leaving the one correct field to define the association.