7 Replies Latest reply: May 6, 2014 9:55 AM by Tom Broens RSS

    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!

        • Re: load inline name group of numbers
          anbu cheliyan

          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?

            • 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

                • Re: load inline name group of numbers
                  anbu cheliyan

                  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

                    • 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

                • Re: load inline name group of numbers
                  ioannis giakoumakis

                  use the interval match function