Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

Consistency check for postal code & city

Hello,

my database consists of two Excel-files:

1. Official data on postal codes and corredponding cities

2. Internal system data which needs to be validated

I want to run a loop which checks the internal system data file (2.) for inconsistencies in the combination of postal code and cityname with the help of the official data file (1.) on postal codes and corresponding cities.

So far i've loaded both excel-files into my qlik-sense-app. How would the script structure look like to realise the consistency checks mentioned above?

Thank you in advance and happy qliking!

official_plz.JPGinternal system_plz.JPG

Nachricht geändert durch Giacinto Abbruzzese

1 Solution

Accepted Solutions
ramoncova06
Valued Contributor III

Re: Consistency check for postal code & city

would be nice to have an example, but you could try with a map

mapping

official:

load

cp&'_'&city as Map,

1

from offical .....

load

system:

cp,

city,

applymap('official',cp&'_'&city,0) as isGood

from system ....


then whatever is has a 0 is bad



5 Replies
prabhu0505
Valued Contributor

Re: Consistency check for postal code & city

Share the files.

ramoncova06
Valued Contributor III

Re: Consistency check for postal code & city

would be nice to have an example, but you could try with a map

mapping

official:

load

cp&'_'&city as Map,

1

from offical .....

load

system:

cp,

city,

applymap('official',cp&'_'&city,0) as isGood

from system ....


then whatever is has a 0 is bad



gino2780
Contributor

Re: Consistency check for postal code & city

I've edited my initial posting and added sample screenshots of the two files.

Re: Consistency check for postal code & city

Hi Giacinto, have you tried what Ramon saids? Using your field names it should be:

official:

mapping load

plz&'_'&ort as Map,

1

from offical .....

system:

load PLZ,

Ort,

applymap('official',PLZ&'_'&Ort,0) as isGood

from system ....

Not applicable

Re: Consistency check for postal code & city

Create a match key in both tables . You can use this to link exact matches . This is what I use for post codes & account names. But any combination can be used

UPPER(LEFT(purgechar([Company Name],chr(32)),3))&'_'&UPPER(LEFT(purgechar(Postcode,chr(32)),5))  as MATCH_ACCOUNT_KEY,

Yours should look something like this

NUM(PLZ,'#####').UPPER(Ort) as MATCH_KEY

you may need to purge unwanted charters as in my example but this depends on your data