I am a new QlikView user. I have searched extensively on this but cannot find a script that works. I have a main table (DETAIL) containing many columns of data that I need to do analytics on. This table is regarding physician spend and each transaction will either have a state license #, EIN # or NPI # (some physicians will have both a state license # and an NPI#) to identify the physician that we have the spend on. I have another table that only has 4 columns, customer ID, state license #, EIN # and NPI#. I tried associating the 2 tables so that customer ID would be pulled into my main table by linking either the state license #, EIN# or NPI# but qlik creates a separate $syn table for state lic, EIN & NPI and customer id is not populated. Is there a way to link the 3 columns from each table so that if either of the fields match, customer id will be pulled into the transaction on the DETAIL table?
If I've understood correctly, you would need to create three mapping tables, one table each for mapping license, EIN and NPI to customer.
Then, in your fact table, you can use nested ApplyMap() functions to first check the most probable mapping, then, if no map found the second mapping table, then the third, Something like
MAP1:
MAPPING
LOAD License, Customer
FROM TABLE2;
MAP2:
MAPPING
LOAD EIN, Customer
FROM TABLE2;
MAP3:
MAPPING
LOAD NPI, Customer
FROM TABLE2;
DETAIL:
LOAD
ApplyMap('MAP1',
License,
ApplyMap('MAP2',
EIN,
ApplyMap('MAP3', NPI, 'Mapping failed') ) ) AS Customer,
