2 Replies Latest reply on Jun 19, 2018 8:56 AM by Catherine March

    Alter multiple fields based on a filter

    Catherine March

      Hello!

       

      I'm learning Tableau Prep and trying to alter a field based on a value in another field. For example, I have state, zip, and location information, and I want to merge Hawaii's information into one location.

       

      For a Region of HI I want to change the Location values to 'Port of LA' and the Postal Code values to '90733'. I figured out how to change 'HI' to 'CA' using the "Group and Replace -> Manual Selection" button, but I need the "HI" region to find all the zip codes and locations that need to be changed.

       

      Current Table:

      Region
      LocationPostal Code
      HIHonolulu96813
      HIHilo96720-5243
      HIWailuku96793-1154
      HIHilo96720-5243
      CAMira Loma91752
      CARiverside92509

       

      Desired Table:

      RegionLocation
      Postal Code
      CAPort of LA90733
      CAMira Loma91752
      CARiverside91752

       

      I've tried to create a new calculated field using

      if [Region] = "HI" AND IF [Location]<>'Port of LA' AND IF [Postal Code] <> '90733'

      then "CA"

      But I can't manage to get all fields changed at the same time.

       

      Thanks in advance!

        • 1. Re: Alter multiple fields based on a filter
          Joshua Milligan

          Catherine,

           

          It looks like you are on the right track!  There are several ways you might do it, here's the way I might choose:

           

          • Rename the Location and Postal Code fields because you will create new fields to replace them.  I like to just put an underscore in front of them so I know what the original name was but it also indicates to me that they are only temporary and will be removed later.  You might come up with a different convention.  So, my fields are now _Location and _Postal Code
          • Create new fields with calculations for Location and Postal Code.  They will implement the logic you described above, but will retain the values present in the original _Location and _Postal Code fields if the location is not "CA"

           

          • Group the CA value as HI for the Region field as you had previously done (you can actually just click on the CA value in the profile pane and type the new value)
          • Remove the _Location and _Postal Code fields as they are no longer needed

           

          Here are the order of Changes as they appear in Prep:

           

          I've attached the flow so you can see it.

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Alter multiple fields based on a filter
            Catherine March

            Thanks Joshua,

             

            I wasn't clear in my initial ask, I was trying to rename the HI locations to the Port of LA in CA, but your logic and steps got me to what I needed! Thank you.