Link table

[wanyunyang]

I would like to join three tables:

Table1: Customer, Date, Business

Table2: County, Business

Table3: County, Customer

To avoid synthetic keys and circular reference, I need to create a link table. I'm not sure how to write the link key. Should the link key just be: 

Table1: Customer&Business, Table2: County&Business, Table3: County&Customer?

[Vegar]

The best way of combining these three tables is to rename the County field names into two unique names. You could choose to join the tables, but you could also leave them as three tables in the datamodel.

Table:
LOAD Customer, Date, Business
FROM Table1;

//LEFT JOIN (Table) //Optional
LOAD  
  County as [Business County], 
  Business
FROM Table2;

//LEFT JOIN (Table) //Optional
LOAD  
   County as [Customer County], 
  Customer
FROM Table3;

[wanyunyang]

Hi Vegar, thanks for replying. I mean County in Table2 and Table3 are the same. I'm just wondering how to join these three tables since each two of them have different synthetic fields.

[Vegar]

An efficient way is to use autonumberhash256() function

autonumberhash256(Customer,Business) as %CustomerBusiness

autonumberhash256(County,Business) as %CountyBusiness

An more ready to read, but still safe to use approach would be

County & '|' & Business as %CountyBusiness

(Assuming you don't have any | in your field values.)

[emrekaya]

I am not sure but No Concatenate in the script may work.