Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Nachricht geändert durch Giacinto Abbruzzese
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
Share the files.
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
I've edited my initial posting and added sample screenshots of the two files.
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 ....
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