Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

load inline name group of numbers

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!

1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: load inline name group of numbers

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

7 Replies
anbu1984
Honored Contributor III

Re: load inline name group of numbers

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
Honored Contributor II

Re: load inline name group of numbers

use the interval match function

Not applicable

Re: load inline name group of numbers

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

namepostal code
jan1000AA
piet1010AA
etcetc

list with postal codes per region:

Postal codesRegion
1000 - 1299Noord-Holland
1300 - 1379Flevoland
etcetc

list with region and ISO 3166-2 codes:

RegionISO code
Noord-HollandNL-NH
FlevolandNL-FL
etcetc

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
Honored Contributor III

Re: load inline name group of numbers

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

Not applicable

Re: load inline name group of numbers

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

iso.png

sorry for the dutch names in the tables. don't think you need them. Mayby only personen=persons and postcodes=postalcodes

anbu1984
Honored Contributor III

Re: load inline name group of numbers

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

Now use table box to display Name and ISO

Not applicable

Re: load inline name group of numbers

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

Community Browser