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

    Alter multiple fields based on a filter

    Catherine March



      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:

      LocationPostal Code
      CAMira Loma91752


      Desired Table:

      Postal Code
      CAPort of LA90733
      CAMira Loma91752


      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



          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!


          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.