2 Replies Latest reply on Aug 21, 2018 9:47 AM by Kevin Swain

    Cleaning Dates field using Tableau Prep

    Kevin Swain

      Hi All

       

      I wonder if I may seek advice, I am a new user of Prep and I am having difficulty cleansing one field in a data set I am looking at

       

      Basically I receive a data set from a client, but they have a single column called Fact_Date and in some instances they have multiple dates in the field for example 07/08/2018/07/08/2018/07/08/2018

       

      I have tried unsuccessfully to filter out the left most part of the date field to show only 07/08/2018

       

      Prep version 2018.1.1

       

      Appreciate any help on this one

       

      Many thanks

       

      Kevin

        • 1. Re: Cleaning Dates field using Tableau Prep
          Joshua Milligan

          Kevin,

           

          A lot depends on whether this example is the pattern for all of the instances of multiple dates, or just one possible pattern.  If it's always that pattern (and assuming you always want the first date) you could create a calculation that gets the leftmost date:

           

          DATE(LEFT([Date Field], 10))

           

          Or, if you wanted the right-most date:

           

          DATE(RIGHT([Date Field], 10))

           

          I hope that works and you can mark this answer correct.  But if the pattern differs at all, that may not consistently work and we might need the examples of the full spectrum of possibilities.

           

          Best Regards,

          Joshua

          • 2. Re: Cleaning Dates field using Tableau Prep
            Kevin Swain

            Thank you for the response Joshua I tried the DATE(LEFT([Date Field], 10)) on a sample data set where every row contained the same length and values but having created a calculation, when applied only returned null values

             

            However what I have now found out and is working for me is to initially change the field from Date format to String then using Left([Date Field],10) I get the left most value

             

            All the best

            Kevin