load inline name group of numbers

[#1]

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!

[anbu1984]

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?

[giakoum]

use the interval match function

[#4]

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

[anbu1984 · Accepted solution]

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

[#6]

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

[anbu1984]

3 tables you created in script and 4th synthetic table created by qlikview.

Now use table box to display Name and ISO

[#8]

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!!