Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Zip Code Ranges

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

1 Solution

Accepted Solutions
MarcoWedel

Hi Jeba,

another solution could be:

QlikCommunity_Thread_128016_Pic1.JPG.jpg

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:

QlikCommunity_Thread_128016_Pic2.JPG.jpg

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

View solution in original post

7 Replies
Not applicable
Author

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

Thanks

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Hello Boss,

Did you had a chance to check my message.

Could you please help me

Thanks & Regards

Jeba

MarcoWedel

Hi Jeba,

another solution could be:

QlikCommunity_Thread_128016_Pic1.JPG.jpg

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:

QlikCommunity_Thread_128016_Pic2.JPG.jpg

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

Anonymous
Not applicable
Author

Hi Marco,

You are really Great.

This works perfectly for my entire range.

Thanks a lot for your timely help.

Thanks & Regards

Jeba