Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

peterkunhardt
New Contributor II

Associating Columns on Partial Match

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?

Community Browser