0 Replies Latest reply: Feb 5, 2018 5:41 PM by Peter Kunhardt RSS

    Associating Columns on Partial Match

    Peter Kunhardt

      I am attempting to create an SMS Monitoring system to evaluate deliverability, and I'm trying to match an SMS message with a recipient phone number to a table of countries with country phone prefixes as the key. I would simply grab the recipient phone # field with something like LEFT(sms.recipient, 4) in order to get a value that matches the country data perfectly, but unfortunately country prefixes and area codes are not of a standard length, and my recipient field does not include a delimiter between the prefix and the rest of the phone number.

       

      In SQL the join is quite easy: with a column on my country data table "Phone_Code" in which the entries appear as:

       

      +1%

      +20%

      +225%

      etc

       

      and phone numbers as:

       

      +10000000

      +560000000

      +2250000000

      etc

       

      I can simply join to the recipient phone:

       

      INNER JOIN country_data ON country_data.phone_code LIKE sms.recipient

       

      But my understanding is that Qlik is looking for an exact match only in this case. Unfortunately I can't effect the join in the load statement (to my knowledge) as the Country Data table is from a different Connection source as the SMS data, (and cannot be moved, SMS data is from a replica db and cannot be modified) so I'm defining the join in the Data Manager.

       

      Thoughts? Suggestions?