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?
