1 Reply Latest reply: Feb 16, 2016 6:53 PM by Stefan Wühl RSS

    Table associating results in $syn table

    David Kwiatkowski

      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
          Stefan Wühl

          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;