Link tables by multiple fields without SYN keys?

[alanmcgrath]

Hi All,

I have an app with several tables and some common fields between them.  I have joined and concatenated the tables to eliminate synthetic keys and now have reduced the model down to 2 very large tables.

These 2 tables are linked by a Product ID code.  In each of the tables there is also are Product name field, the Product names within the field are similar to each other.  Right now both these Product names are named differently within the separate tables so they don't create synthetic keys and errors.  However, I want to be able to join/link the tables based on master Product Name (perhaps using a mapping load) as well as still having it joined/linked at Product ID.

I've tried concatenating the 2 tables into one table but that creates a table that doesn't work for the analysis I'm trying to do.  I tried mapping the names but I still get synthetic keys.

Is there anyway I can have these tables joined/linked using the Product ID and a master Product Name without getting errors in my data or synthetic keys? Seems like there may be a simple solution but I can't seem to figure it out.

Thanks!

[b_garside]

Your on the right track I believe based on what I'm reading.

ApplyMap() used in your script should solve this quite efficiently and not add any new tables.

You just need to create a distinct lookup list of all the Product names and map from the different fields as your 'Master Product Name'

[alanmcgrath]

I'm still getting SYN keys,

So my tables are linked on Product ID, then I do the mapping as follows:

Product_Table:

Load *,

ApplyMap('NameChange',Name1,Null()) as MasterName

Resident Products;

Drop Table Products'

Sales_Table:

ApplyMap('NameChange',Name2,Null()) as MasterName

Resident Sales;

Drop Table Sales;

After running script it is linked on the keys Product ID and MasterName but I'm getting the SYN key there. Am I doing this correctly?

[b_garside]

This looks correct syntax wise. Don't know how your Mapping table 'NameChange' is setup though.

If you have two Master Names that are the exact same name you will get an error I would think.

[alanmcgrath]

Ok, thanks.  My issue is I want the names to be the same so when I filter on the name it brings data from both tables but I can't break the link on the Product ID.  Is there a way to do this without SYN keys?

[#6]

You can add a new field, say Product Key, in the load script of both tables, which is a concatenation of Product ID and Product Name. Keep the names of Product ID and Product Name different in the two tables. This will join the two tables on Product Key and there will be no synthetic keys.

[Tab A]:

Load

[Product ID] As A_PID,

[Product Name] As A_PName,

[Product ID] & [Product Name] As Product_Key

Resident A;

Drop Table A;

Inner keep

[Tab B]:

Load

[Product ID] As B_PID,

[Product Name] As B_PName,

[Product ID] & [Product Name] As Product_Key,

Count

Resident B;

Drop Table B;