topic Re: Zip Code Ranges in QlikView

Zip Code Ranges

Anonymous — Fri, 01 Aug 2014 16:20:33 GMT

Dear Experts,

Can anyone share if you have already created such similar application.

My company warehouses were mapped to the Zip Code ranges.In one table I will have the Zip Code range from and Two with the assigned ware house codes which is my master data.

Second in the transactions table I will have the actual zip codes.Now I want to map the Warehouses to these zip codes if it is within the range.

Since I have some text codes also I am finding some difficulties in mapping.

Attaching the sample data for your kind reference.

Could you please help me.

Thanks & Regards

Jeba

Re: Zip Code Ranges

Fri, 01 Aug 2014 17:28:50 GMT

Attached with answer. Hope it helps you.Please take a look at the back-end script.

Thanks

Re: Zip Code Ranges

Anonymous — Sat, 02 Aug 2014 05:58:59 GMT

Hi Ajay,

Thanks for the help.

Its exactly what I was looking for.

I have one last query here.

Is there a way we can modify the below code to refer from the database/qvd.Because I have a huge database of Zip codes,so we can't make a static codes for this.

Could you please help me.

* inline [
Zip Codes
98
78
29
23
A0A
A1D
Q0A
Q0B
Q1C
];

Thanks & Regards

Jeba

Re: Zip Code Ranges

Sat, 02 Aug 2014 06:09:01 GMT

Hey Jeba,

You could maybe read from the database and then store it in a qvd. And then similar to an inline table, load the qvd with the required fields and apply the same logic. In real situations the Zipcode table would definitely be having many more fields. All those can be just included as well.

Thanks

Re: Re: Zip Code Ranges

Anonymous — Sat, 02 Aug 2014 06:20:03 GMT

Hi Ajay,

I tried loading the data from the base and its worked,however the output seems wrong

Could you please help me what I am doing wrong here.

Attaching the sources for your kind reference.

Kindly help me,I was trying this for a long time and it will be great if you help me with the solution

Thanks & Regards

Jeba

Re: Zip Code Ranges

Anonymous — Sat, 02 Aug 2014 07:23:58 GMT

Hello Boss,

Did you had a chance to check my message.

Could you please help me

Thanks & Regards

Jeba

Re: Zip Code Ranges

MarcoWedel — Sun, 03 Aug 2014 01:16:16 GMT

Hi Jeba,

another solution could be:

I tried to create additional *num columns that treat the alphanumeric ZIP codes as base36 coded and converted these Zip Codes accordingly into decimal values:

decimal Zip Codes in the base table where truncated as only the first 3 places seemed to match.

I didn't have time to optimize the code, so it's a bit longer than it should:

tabRange:
LOAD [Zip Code_From],
    [Zip Code_From Num],
    [Zip Code_To],
    Sum(summand) as [Zip Code_To Num],
    [WH Code],
    RecNo
Group By [Zip Code_From], [Zip Code_From Num], [Zip Code_To], [WH Code], RecNo;
LOAD *,
    If(
        IsNum([Zip Code_To]),
        [Zip Code_To],
        If(
          IsNum(Mid([Zip Code_To],IterNo(),1)),
          Mid([Zip Code_To],IterNo(),1),
          Ord(Mid([Zip Code_To],IterNo(),1))-55
          )*pow(36, Len([Zip Code_To])-IterNo())
      ) as summand
While IterNo()<=Len([Zip Code_To]) and not IsNum([Zip Code_To]) or IterNo()=1 and IsNum([Zip Code_To]);
LOAD [Zip Code_From],
    Sum(summand) as [Zip Code_From Num],
    [Zip Code_To],
    [WH Code],
    RecNo
Group By [Zip Code_From], [Zip Code_To], [WH Code], RecNo;
LOAD *,
    If(
        IsNum([Zip Code_From]),
        [Zip Code_From],
        If(
          IsNum(Mid([Zip Code_From],IterNo(),1)),
          Mid([Zip Code_From],IterNo(),1),
          Ord(Mid([Zip Code_From],IterNo(),1))-55
          )*pow(36, Len([Zip Code_From])-IterNo())
      ) as summand
While IterNo()<=Len([Zip Code_From]) and not IsNum([Zip Code_From]) or IterNo()=1 and IsNum([Zip Code_From]);
LOAD [Zip Code_From],
    [Zip Code_To],
    [WH Code],
    RecNo() as RecNo
FROM [http://community.qlik.com/servlet/JiveServlet/download/580860-118538/Test.xlsx]
(ooxml, embedded labels, table is Range);

tabBase:
LOAD [Zip Codes],
    Sum(summand) as [Zip Codes Num],
    RecNo2
Group By [Zip Codes], RecNo2;
LOAD *,
    If(
        IsNum([Zip Codes]),
        Left([Zip Codes],3),
        If(
          IsNum(Mid([Zip Codes],IterNo(),1)),
          Mid([Zip Codes],IterNo(),1),
          Ord(Mid([Zip Codes],IterNo(),1))-55
          )*pow(36, Len([Zip Codes])-IterNo())
      ) as summand
While IterNo()<=Len([Zip Codes]) and not IsNum([Zip Codes]) or IterNo()=1 and IsNum([Zip Codes]);
LOAD [Zip Codes],
    RecNo() as RecNo2
FROM [http://community.qlik.com/servlet/JiveServlet/download/580860-118538/Test.xlsx]
(ooxml, embedded labels, table is Base);

Left Join (tabBase)
IntervalMatch ([Zip Codes Num])
LOAD [Zip Code_From Num], [Zip Code_To Num]
Resident tabRange;

Left Join (tabBase)
LOAD [Zip Code_From Num], [Zip Code_To Num], [WH Code]
Resident tabRange;

DROP Fields [Zip Code_From Num], [Zip Code_To Num] From tabBase;

Another possibility could be to create a VBS-function for the base36 conversion.

This should lead to shorter and more readable script code.

hope this helps nevertheless

regards

Marco

Re: Zip Code Ranges

Anonymous — Sun, 03 Aug 2014 07:28:33 GMT

Hi Marco,

You are really Great.

This works perfectly for my entire range.

Thanks a lot for your timely help.

Thanks & Regards

Jeba