Synthetic key between 2 tables

[umashankarus]

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

[isingh30]

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

[yujiyamane]

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.

[ziadm]

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 ;

[jonathandienst]

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.

[sasiparupudi1]

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;