Phone numbers on different formats. How to match?

[andy]

Hi folks,

I'm into writing a small Qlikview script to see which users that should be added to a system.

I have a database with users name and phone-number. I also have a file containing a list of users that might already exist in the database.

The data I have to match on is name and phone number. One or the other or both.

My small problem here is that the phone numbers in the database are stored in different ways for example as

070-123456

070-12 34 56

070-123 456

070123456

+44 70123456

+44 70 123456

and so on

The same goes for the file.

I would like to know if there is any smart way in Qlikview to achieve a match on phone-numbers with these pre-conditions?

My approach would be to get the DB-users into Qlikview, export as file and then get all phonenumbers on the same format with regular expressions. Run the same regular expression for the file. Then load both files into Qlikview.

/Andy

[#2 · Accepted solution]

There are different way of doing this,

the string functions may helps you :

Suppose your Field Name is PhoneNumber,

the PurgeChar function : PurgeChar( [PhoneNumber], ' -')
will load Phone number without any char in the second parameter : here , ' -' means that every space and every - will be erased.

Then to manage the +44 : use replace ([PhoneNumber],'+44','0') : All +44 will be replaced by 0

in the script you can use the following syntax :

LOAD
PurgeChar( Replace([PhoneNumber],'+44','0'), ' -') as PhoneNumber ,
...

Note: I've written this from memories, I haven't tested it on a Qvw, all apologies if I made a syntax error 🙂

Hopes it help

[andy]

Awesome, thanks man!

brgds

Andy