Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Table associating results in $syn table

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

1 Reply
swuehl
Not applicable

Re: Table associating results in $syn table

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;