3 Replies Latest reply on Mar 23, 2016 4:56 PM by Adam Crahen

    Using post codes in multiple regions

    Sue Vlotman

      I have regions, each with a list of post codes.  There is some overlap in post codes where 1 post code can be in two regions.  For example region 1 = 2643, 2644, 2645 and 2646.  Region 2 = 2644, 2650, 2651 and 2653.  I have set up a Parameter called Territory with Region 1 and Region 2. Then tried to use Case in the formula below, but I get the error Expected 'END' to match 'Case' at character 0. Any suggestions?

       

      Case [Territory]

      WHEN 'Region 1' THEN IF  [Post code] = '2643' or [Post code] ='2644' or [Post code] = '2645' or [Post code] = '2646' THEN 'Region 1' END

      WHEN 'Region 2' THEN IF [Post code] = '2644' or [Post code] = '2650' or [Post code] = '2651' or [Post code] = '2653' THEN 'Region 2' END

       

      I also tried

      Case [Territory]

      WHEN 'Region 1' THEN IF  [Post code] = '2643' or [Post code] ='2644' or [Post code] = '2645' or [Post code] = '2646' THEN [Post code] END

      WHEN 'Region 2' THEN IF [Post code] = '2644' or [Post code] = '2650' or [Post code] = '2651' or [Post code] = '2653' THEN [Post code] END

        • 1. Re: Using post codes in multiple regions
          Adam Crahen

          Try this, but you are going to get nulls for all the other postal codes not referenced.  If you already have a field with the Region in it, then before the red END text below insert ELSE [Region]

           

          IF [Territory] = 'Region 1'

              THEN IF  [Post code] = '2643' OR [Post code] ='2644' OR [Post code] = '2645' OR [Post code] = '2646' THEN 'Region 1' END

          ELSEIF [Territory] = 'Region 2'

              THEN IF [Post code] = '2644' or [Post code] = '2650' or [Post code] = '2651' or [Post code] = '2653' THEN 'Region 2' END

          END

          • 2. Re: Using post codes in multiple regions
            Sue Vlotman

            Hi Adam

            Thanks for the reply.  I am getting a different error message now Cant compare integer and string values and the = sign is underlined in red.

            I want to use this as a filter to choose one or multiple territories.  I have set up an example with the Territory filter containing the formula below.  There is one worksheet showing what I am trying to achieve.  In the example each post code is only in one group.  I need a post code to be able to go into more than one group.  I hope I have saved the workbook at:  https://public.tableau.com/profile/publish/Territorysetup/Sheet1#!/publish-confirm

             

            IF [Territory] = 'RIB'

                THEN IF  [Post code] = '2643' OR [Post code] ='2644' OR [Post code] = '2645' OR [Post code] = '2646' THEN 'RIB' END

            ELSEIF [Territory] = 'RIR'

                THEN IF [Post code] = '2644' or [Post code] = '2650' or [Post code] = '2651' or [Post code] = '2653' THEN 'RIR' END

            END

             

            Any assistance would be appreciated.

            • 3. Re: Using post codes in multiple regions
              Adam Crahen

              Hi Sue-

               

              Your Postal Code is a #.

               

               

              If you remove the single quote around the postal codes or wrap [Postal Code] with str(), it will be a valid calc.