6 Replies Latest reply on Apr 5, 2016 3:13 AM by Simon Runc

    is there a simple "Find" and "Replace" data in Tableau?

    endi nguyen

      In one of many extracts in my Tableau file, I need to update certain data to something different than it was originally input. i.e. "NYC" to be "Newyork". I can use calculated field to change, but the field is used across multiple worksheets and dashboards (Designed by different users) hence it's very difficult to debug which ones are linked. Is there a simple "find" and "replace" as in MS.Excel that I don't have to create a new calculated field then relink?

        • 1. Re: is there a simple "Find" and "Replace" data in Tableau?
          Sankarmagesh Rajan

          Hi endi,

           

          We can use alias and replace the names. Have you tried alias in your case.

           

          Thanks

          sankar

          • 2. Re: is there a simple "Find" and "Replace" data in Tableau?
            kumar.c

            Hi,

            Yes you can replace the NYC with Newyork in excel like below screenshotExcel Replace.png

             

            or you can change the data in a particular column as alias in Tableau like below screenshot:

            Alias.png

            Click on alias and then change the particular value as required as below:

            Alias-1.png

            • 3. Re: is there a simple "Find" and "Replace" data in Tableau?
              endi nguyen

              May I know, by using Alias as above, will it impact the filter values?

              For example: in one extract, half of data in that city column is "NYC", then now i append new data to this extract with "Newyork". editing Alias as above will make everything "Newyork" --> this is ok

              - However, in other dashboards, my filters (which link to this extract) is now "Newyork". By selecting "Newyork", is the original "NYC" included in the selection?

              • 4. Re: is there a simple "Find" and "Replace" data in Tableau?
                Simon Runc

                hi Endi,

                 

                I think the replace references feature will do what you need.

                 

                Right Click on the field you want to replace (in your case the NYC) and select 'Replace References'

                 

                Selecting this will bring you up a list of all the fields

                 

                Where you can now choose the field to 'switch' it with.

                 

                If it that's you have IF [city] = 'NYC' THEN..END in lots of calculated fields and now you want it to read IF [city] = 'newyork' THEN..END, then there isn't (out the box) a feature to do this. My advice (and what I tend to do for these kind of 'changeable' fields) is to have a single calculated field which does the various 're-assignments', and then use this in any 'dependent' formulas

                 

                so something like

                [MyCity]

                CASE [city]

                     WHEN 'NYC' THEN 'New York'

                     WHEN 'WDC' THEN 'Washington DC'

                ...

                END

                 

                and then any subsequent formulas just reference [MyCity]...

                IF [MyCity] = 'New York' THEN...END

                 

                This way if it changes I only have to change one formula...won't help you now! but once you've done the exercise of setting up the calcs this way you'll be (fairly) future-proofed!

                • 5. Re: is there a simple "Find" and "Replace" data in Tableau?
                  endi nguyen

                  Thanks all for very prompt and detailed steps.

                  - I couldn't use the calculated filed here because it's too complicated to reassign the filter to the new fields.

                  - For changing alias above, since half data is "NYC" (=member), the moment I changed to alias "Newyork", Tableau won't allow due to half of the new appended data also has member of "Newyork" as alias "Newyork". Unless I create a new name for these two. But then the new name will not sync with other dashboards where "Newyork" is used.

                  • 6. Re: is there a simple "Find" and "Replace" data in Tableau?
                    Simon Runc

                    hi Endi,

                     

                    Now if this will work, will depend on your exact data. However if I understand correctly...you have a field called [city] which comes in from your database. You then reference this field in multiple calculations where you have IF statements to 're-assign' values to a single name for a city. You can use a combination of my suggestions.

                     

                    So first create a field called [city to use] (or similar)...and (for now) just make it the text 'city'

                     

                    Next use the 'Replace Reference' I detailed above, to replace the reference for [city] to [city to use]....so now all your if statements that were using [city] are now using [city to use]...

                     

                    Now change the calculated field in [city to use] to the re-assignment formula

                    CASE [city]

                         WHEN 'NYC' THEN 'New York'

                         WHEN 'WDC' THEN 'Washington DC'

                    ...

                    END

                     

                    the reason we initially set up the [city to use] with a generic 'city' is that if we created the final formula first, when we replace references is would also change where we have use [city] in that calculated field (in fact I'm not 100% sure what it would do as we'd have a self-referencing formula!..but simpler just to do it as above!!)

                     

                    You will now have the set up where every calculated field is using [city to use], and you only ever need to change this if you get other forms of 'new york'...this also means you can re-assign several different versions of 'new york'...eg

                    CASE [city]

                         WHEN 'NYC' THEN 'New York'

                         WHEN 'NewYork' THEN 'New York'

                    ...

                    END

                     

                    Hope that does the trick, and makes sense.