topic Re: Synthetic key between 2 tables in QlikView

Synthetic key between 2 tables

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts

I have rows in two tables which has only of the key match

However the synthetic key is based on 2 fields. So only rows that matches both the fields are listed

I want to list rows even if one of the key is matched

How to resolve this ?

Thanks

Umashankar

Re: Synthetic key between 2 tables

isingh30 — Sun, 11 Mar 2018 22:59:01 GMT

Your question seems confusing to me. You want to remove synthetic key? if yes, then just rename the second .field

Re: Synthetic key between 2 tables

yujiyamane — Sun, 11 Mar 2018 23:16:50 GMT

A data model should always be a star schema.

I would create one fact table which contains two keys and two dimension tables for gtin_link_case and gtin_link_retail.

Re: Synthetic key between 2 tables

ziadm — Mon, 12 Mar 2018 00:10:33 GMT

use

AutoNumberHash128 to generate a key and drop the fields from the table

Load * ,

AutoNumberHash128(gtin_link_retail,gtin_link_case ) as LinkKey

From Table1

Load * ,

AutoNumberHash128(gtin_link_retail,gtin_link_case ) as LinkKey

From Table2

drop Fields gtin_link_retail, gtin_link_case from Table2 ;

Re: Synthetic key between 2 tables

jonathandienst — Mon, 12 Mar 2018 05:05:39 GMT

A synthetic key a simply an association through more thane one field. Although this is not a problem itself, synthetic/compound key are often the result of a faulty model design. And in the worst cases, the model design will result in a whole constellation of synthetic keys, which almost certainly cause problems in the metrics

A single synthetic key should behave in the way you require (and much the same as a join in SQL). Selecting one value of the two should select all the rows in both tables which match that value. But I would still give a thought to the model design and ask yourself what each field means, whether the tables could be concatenated and whether the compound key is necessary.

Re: Synthetic key between 2 tables

sasiparupudi1 — Mon, 12 Mar 2018 05:10:42 GMT

Table1:

Load

Code,

gtin_link_retail&'|'&gtin_link_case as Key

table1;

Left join

Load

gtin_link_retail&'|'&gtin_link_case as Key

gtin_link_retail,

gtin_link_case

table2;