How can I match one set of client numbers with a prefix against those without?

[simongoodman]

I am using two databases to combine information. Unfortunately there are cases when the client name has been entered slightly differently in both. One database being in all UPPER case and the other upper and lower case characters. Sometimes clients end as LTD and but in the other database as Limited.

Also the client numbers are presented differently, one being all numerical, running from six to seven numbers, e.g. 1234567 or 123456. While in the other database there is a prefix of a letter and two zeros, e.g. L001234567 or L00123456. So the length of the client number varies.

This means there can be two versions of the same client, with different data associated to each name in my qvw.

How can I match the client numbers? Once matched is there a way to merge these almost duplicate client names?

I understand a non Qlikview solution would be to ensure client names are the same in both databases.

[#2 · Accepted solution]

Hi,

it is better to work with client numbers if you have them available as much as names. Try with

num(purgechar('L001234567','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'))

* put in place of 'L001234567' the ClientNumber Field name

to get rid of the letters in the numbers from the second datasource, if you are certain that only letters are applied to the number. The num will get rid of the zeroes. Once you match the numbers and if the other fileds are identical you can load both tables with Distinct to get rid of the repetitions, like

T1:

LOAD

     ClientNumber,

     Field1,

     Field2,

     etc,

FROM source1;

LOAD DISTINCT

     ClientNumber,

     Field1,

     Field2,

     etc,(same names and number of fields as above to ensure concatenation)

FROM source2;

the DISTINCT qualifier will apply to both tables after they get (automatically) concatenated.

If you have different data against identical client numbers the data won't get reduced.

Hope this is useful to you.

B.

[simongoodman]

Thanks for the solution which I like.

As always I have found data complications where some client numbers when stripped of alphabetic produce duplicate numbers in one datebase. Also some client numbers which are different in the two databases for the same client name. But your solution preformed the job as spec'ed.

Simon