How integrate a table with two fields which refer to two different tables?

[PescheSwitzerland]

Dear Community 🙂

Here the "challenge":

I want to link a table that has three fields: "Product ID", "Supplier No" and "is_DefaultSupplier" as this table shows the Default Supplier every product with the Supplier No.

In the DataModel (attached), I have a table "ProductDim" with the field "Product ID" and a table "Supplier" with "Supplier No". 

How can I link my new table? 🙂 Do I have to go via the LinkTable?

Thanks a lot and greetings from Switzerland!

Pesche

[steeefan]

Hoi Pesche,

If in the key table LinkTablePurchaseIncomingGoods you have data for both %KEY_PRODUCT and %KEY_SUPPLIER on each row, you could try using ApplyMap():

mapDefaultSupplier:
MAPPING LOAD
  [Product ID] & '|' & [Supplier No],
  is_DefaultSupplier
RESIDENT
  DefaultSupplierTable;

// Then when creating the link table:
LinkTablePurchaseIncomingGoods:
LOAD
  ApplyMap('mapDefaultSupplier', %KEY_PRODUCT & '|' & %KEY_SUPPLIER, 0) AS isDefaultSupplier,
  ...
RESIDENT
  ...;

[PescheSwitzerland]

Thanks a lot for your reply. I did all that but it isn't integrated in the link table 😞 please see attached and thanks for your help!

[steeefan · Accepted solution]

I was merely showing sample code, not the exact way of how to achieve this in your specific case.

Put the MAPPING LOAD before the creation of the key table LinkTablePurchaseIncomingGoods. After the MAPPING LOAD, you have to DROP the table from which you get that mapping data: DROP TABLE product_supplier_link;. Otherwise that will remain in your data model.

You then have to add the ApplyMap() command right there where the key table LinkTablePurchaseIncomingGoods is created, not create a new one by the (almost) same name.

The following code assumes that you have already loaded the table product_supplier_link into your data model.

// Create mapping table from product_supplier_link
mapDefaultSupplier:
MAPPING LOAD
  [Product ID] & '|' & [Supplier No],
  is_DefaultSupplier
RESIDENT
  product_supplier_link;

DROP TABLE product_supplier_link;


// Then when creating the link table:
LinkTablePurchaseIncomingGoods:
LOAD
  ApplyMap('mapDefaultSupplier', %KEY_PRODUCT & '|' & %KEY_SUPPLIER, 0) AS isDefaultSupplier,
  ...
RESIDENT
  ...;

[PescheSwitzerland]

Thank you so much! That's it! I've learned a lot from you! Thanks!! 🙂