topic Re: Table associating results in $syn table in QlikView

Table associating results in $syn table

Tue, 16 Feb 2016 14:48:31 GMT

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?

Thanks,

Dave

Re: Table associating results in $syn table

swuehl — Tue, 16 Feb 2016 23:53:38 GMT

Look into a MAPPING table approach:

Don't join - use Applymap instead

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

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,

...

FROM DETAIL;