topic Re: Join two tables on partial match in App Development

Join two tables on partial match

Mathieu_ROY — Sat, 16 Nov 2024 20:47:22 GMT

Hi guys,
I'm currently having difficulties joining two tables (of different sizes) based on a partial match. Those two tables are from QVDs created from the DB (no modification, just a load of the data in the QVDs).
I have a table "countries" registering the different countries and their international mobile prefixes:

countries:
LOAD
prefix,
name
FROM [lib://Qlik_QVD/countries.qvd]
(qvd);

Then, I have a table "messages_stats" regrouping different information on mobile messages:

messages_stats:
LOAD
id,
......
msisdn,
FROM [lib://Qlik_QVD/messages_stats.qvd]
(qvd);

In this table, "msisdn" is the mobile phone number of the recipient of the message.
For example, in France, the msisdn is: 33X XX XX XX XX

In the table "countries", the prefix for the country "France" is "33".

Thus, I want to join the two tables on this partial match.

As information, in MYSQL here is the (working) request:

From messages_stats
LEFT JOIN countries ON messages_stats.msisdn LIKE CONCAT(countries.prefix , '%')

Thanks for your answers guys!

Re: Join two tables on partial match

marcus_sommer — Tue, 28 May 2019 12:13:59 GMT

You could do it with a mapping like:

countries:
mapping LOAD prefix, name FROM [lib://Qlik_QVD/countries.qvd] (qvd);

messages_stats:
LOAD
id,
......
msisdn,
applymap('countries', left(msisdn, 2), 'no country') as Country
FROM [lib://Qlik_QVD/messages_stats.qvd] (qvd);

- Marcus

Re: Join two tables on partial match

Mathieu_ROY — Tue, 28 May 2019 12:24:00 GMT

Hi Marcus,

My bad, I forgot another crucial information: the prefixes are 1,2,3 and 4 numbers long .....
So in this case, if I'm not mistaken, "left(msisdn,2)" doesn't work.

I think I need to use the "wildmatch" function, but i'm not sure how.

- Mathieu

Re: Join two tables on partial match

marcus_sommer — Tue, 28 May 2019 12:40:55 GMT

I hope there is any delimiter within your msisdn to identify the prefix otherwise the match-values aren't mandatory unique and it could give you a wrong match. Nevertheless you could try it with a nesting of the mapping like:

applymap('countries', left(msisdn, 4),
applymap('countries', left(msisdn, 3),
applymap('countries', left(msisdn, 2),'no country'))) as Country

- Marcus

Re: Join two tables on partial match

Mathieu_ROY — Wed, 29 May 2019 07:11:44 GMT

Hi Marcus!

The prefixes are well thought out, in the sense that if a prefix = 33, then no prefix = 33x, so no mismatch.

Still, I applied the nesting and got as a result a field Country full of 'no country'. I checked the QVD 'countries', no problem in here, same for the field "msisdn" from messages_stats. So I don't understand where the error is coming from.
I even tried: applymap('countries', left(msisdn, 2),'no country') as Country to see if the nesting of the mapping was the problem, still full of 'no country'.

Re: Join two tables on partial match

marcus_sommer — Wed, 29 May 2019 08:24:51 GMT

I'm not absolutely sure if the data-type of numeric or string values has an impact by applying a mapping but with text() or num#() you could change it, maybe with:

applymap('countries', num#(left(msisdn, 2), '00'),'no country')

If this doesn't help it means that there other chars included either within the mapping-table or the fact-table. To be sure what they contain you could display them within an UI object and copy the values from there to an editor like notepad++.

Quite often it are any kind of spaces or special chars and a cleaning in Qlik could be look like:

trim(Field) or maybe keepchar(Field, '0123456789')

- Marcus