Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my Qlikview document I need a list of birth regions. I do have postal codes and I have a list of which postal codes belong to what region.
My problem is: how do I load this in the SQL load file?
example:
if the postal code is between 1000 and 1299 it has to load Noord-Holland, between 1300 and 1379, Flevoland and so on.
We are talking about numbers from 1000 until 9999 so writing them all under each other would be a horrible task.
I would appreciate all the help you can give me!
Sql Select Name, Left(PostalCode,4) as PostalCode from Table1
Sql Select SubField(PostalCode,'-',1) as BeginPostalCode, SubField(PostalCode,'-',2) as EndPostalCode, Region From Table2
IntervalMatch(PostalCode) Sql Select BeginPostalCode, EndPostalCode From Table2
Then you can join the result of above sql to your third table to get ISO code
Table1 has Postal code
Table2 has Postal code range, region
So do you want to get region for Postal code in table1? Is this your problem?
use the interval match function
i have a table with people and there postal code. What i want is an ISO 3166-2:NL code attached to each person. It is for mapping.
I found a list on the internet with postal code ranges and a table with ISO 3166-2:NL codes matching the region
so i have this:
table 1: person
name | postal code |
jan | 1000AA |
piet | 1010AA |
etc | etc |
list with postal codes per region:
Postal codes | Region |
1000 - 1299 | Noord-Holland |
1300 - 1379 | Flevoland |
etc | etc |
list with region and ISO 3166-2 codes:
Region | ISO code |
Noord-Holland | NL-NH |
Flevoland | NL-FL |
etc | etc |
Now i want to match the ISO code to the person so Jan and Piet both live in ISO code NL-NH
The mapping works only with the ISO codes
Sql Select Name, Left(PostalCode,4) as PostalCode from Table1
Sql Select SubField(PostalCode,'-',1) as BeginPostalCode, SubField(PostalCode,'-',2) as EndPostalCode, Region From Table2
IntervalMatch(PostalCode) Sql Select BeginPostalCode, EndPostalCode From Table2
Then you can join the result of above sql to your third table to get ISO code
it didn't work with sql select, but i searched a couple of pages about IntervalMatch and found that i could do it like this:
personen:
LOAD Stad,
postcode,
Left(postcode,4) as postcode_cijfers,
Land,
Aantal
FROM
[Maps test.xlsx]
(ooxml, embedded labels, table is Blad1);
postcodes:
LOAD A as BeginPostcode,
B as EindPostcode,
C as Provincie,
D as ISO
FROM
[Maps test.xlsx]
(ooxml, explicit labels, table is Blad2);
IntervalMatch:
IntervalMatch(postcode_cijfers)
load Distinct BeginPostcode, EindPostcode Resident postcodes;
problem is that he gives me 4 tables now
sorry for the dutch names in the tables. don't think you need them. Mayby only personen=persons and postcodes=postalcodes
3 tables you created in script and 4th synthetic table created by qlikview.
Now use table box to display Name and ISO
i know about the 4 tables, didn't knew if the 4th table should be there. but it works so thank you very much! the mapping is working as wel!!