Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gino2780
Creator
Creator

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
Specialist III
Specialist III

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



View solution in original post

5 Replies
prabhu0505
Specialist
Specialist

Share the files.

ramoncova06
Specialist III
Specialist III

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
Creator
Creator
Author

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

rubenmarin

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

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