10 Replies Latest reply on Oct 4, 2012 11:04 AM by Aaron Anzalone

    Zip Codes unrecognized in workbook

    Aaron Anzalone

      I have a weird one. I am creating a radius filter for zip codes on an existing workbook. I have two zip code dimensions (o_zip and d_zip). When I add them to my existing workbook as another data source (no relationship between them, just 2 data connections), neither o_zip or d_zip zip codes are recognized as mappable. However, if I create a new workbook and double click either o_zip or d_zip to map them, all of the zip codes are recognized and map correctly.

      Any help on how I can resolve this error?

        • 1. Re: Zip Codes unrecognized in workbook
          Jonathan Drummey

          I'm thinking that maybe your blend or join isn't working properly, so Tableau doesn't know how to treat the zip codes you are adding in the 2nd data source. Any chance you can post a packaged workbook (.twbx) with some sample data?

           

          Jonathan

          • 2. Re: Zip Codes unrecognized in workbook
            Aaron Anzalone

            Hi Johnathan,

             

            Thanks for taking a look! I've attached the workbook here.

             

            Thank you,

            Aaron

            • 3. Re: Zip Codes unrecognized in workbook
              Jonathan Drummey

              I'm not clear on your goal. It looks like you have the TCPN Sales data, which has one Zip Code, and you want to relate that to one or the other (or both?) of the zip codes in the t_zipcode_distance data, to get a distance? If you want to get a single distance, you're going to need two zip codes in the TCPN Sales data to set up a join or blend, because right now there for each d_zip there are several to dozens of o_zip's and vice versa.

               

              Jonathan

              • 4. Re: Zip Codes unrecognized in workbook
                Aaron Anzalone

                Update: If I open the workbook while using an extract, everything works. If I connect directly to SQL, it does not work.

                • 5. Re: Zip Codes unrecognized in workbook
                  Aaron Anzalone

                  Hi Jonathan,

                   

                  The goal is to show "Hot Vendors Within 100 Mile Radius of Customer" when a particular customer is selected. The zipcode_distance dataset provides which zip codes are within 100 miles of the current customer's zip code.

                   

                  Thank you,

                  Aaron

                  • 6. Re: Zip Codes unrecognized in workbook
                    Jonathan Drummey

                    There are a couple of places where Tableau's functionality varies between extracts and SQL that I know of, I took a quick look at your workbook and didn't see them. This might be one for Tableau tech support.

                    1 of 1 people found this helpful
                    • 7. Re: Zip Codes unrecognized in workbook
                      Aaron Anzalone

                      Ok, cool. Thanks for taking a look!

                      • 8. Re: Zip Codes unrecognized in workbook
                        Jonathan Drummey

                        So do you just want to show the points around the selected customer then? And which field in the t_zipcode_distance data set is linked to the Zip Code in the TCPN Sales data? In that case, it might just be a matter of editing the relationships.

                        • 9. Re: Zip Codes unrecognized in workbook
                          Jonathan Drummey

                          I've got another thought...when you are mapping, you really need to have a row in the data for every point. If you are trying to draw lines from one point to another, then you need a row for each start/endpoint (even if there are a lot of the same start points).

                           

                          With two data sources and trying to blend in Tableau, the blend is only going to return one row from the secondary data source - it's like an aggregated left join. So for each customer, you won't get the N rows, you'll get one row. You're going to need to do a left join either in the Tableau data source or the underlying query to generate the N rows per customer.

                           

                          Jonathan

                          • 10. Re: Zip Codes unrecognized in workbook
                            Aaron Anzalone

                            Yeah, I noticed that after I began. I've written a SQL function that I'm hoping to use as a RAWSQL function to get the SUM([Sales]) for each vendor within the radius. d_zip is the destination zip and o_zip is the originating zip.

                             

                            CREATE FUNCTION [dbo].[fn_SalesInRadius]

                             

                             

                            (

                                        @zip varchar(5)

                                      , @radius int

                                      , @vendorName varchar(255)

                                      , @beginDate date

                                      , @endDate date

                            )

                             

                             

                            RETURNS decimal(18,2)

                            AS

                            BEGIN

                                      declare @Result as decimal(18,2)

                             

                                      set @Result = (select sum(sales)

                                                                              from t_TableauReport

                                                                              where [Zip Code] in (select d_zip from t_zipcode_distance where o_zip = @zip AND @radius < 100)

                                                                                        and

                                                                                                  [Vendor Name] = @vendorName

                                                                                        and

                               [Tableau Date] between @beginDate and @endDate

                                                                      )

                             

                             

                             

                             

                                      -- Return the result of the function

                                      RETURN @Result

                             

                             

                            END