3 Replies Latest reply on Jan 25, 2014 1:43 PM by Shawn Wallwork

    facebook date parse

    alexander.petrik

      Hi! Realy need your help, please!

       

      I have a dataset, stored in mysql database which was scraped from facebook group. Everything is fine but date...

       

      The date column holds date of several types: normal date, which could be parced by tableau DATEPARSE function, and At the same time, it has "related date" like "yesterday at 3:06am".

       

      POSTDATE

      Sep 24, 2011

      May 24, 2013

      Tuesday at 10:11pm

      Aug 15, 2013

      Jan 29, 2013

      Jan 12

      Apr 24, 2012

      Jan 14, 2013

      Dec 17, 2013

      Jan 3

      Jan 3

      May 21, 2011

      Mar 3, 2012

      Feb 3, 2012

      Jan 9

      Jun 16, 2013

      Apr 27, 2013

      Jan 8

      Feb 8, 2012

      Feb 18, 2013

      Apr 9, 2013

      Feb 12, 2012

      11 hours ago

      Sep 5, 2013

      Yesterday at 3:06am

       

      If we know, that date was scraped on 22 jan 2014, can we normalize this data an get tableau readable date column?

      The mysql database is a readonly, so I hope to do this only by tableau tools...

       

      Please, help!

        • 1. Re: facebook date parse
          Shawn Wallwork

          Alexander, the attached file will get you going in the right direction, but as the workbook title says: Tableau Is Not an ETL tool. Best (in my opinion) to clean your data before bringing it into Tableau.

           

          Cheers,

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Re: facebook date parse
            alexander.petrik

            Mr. Shawn Wallwork showed me the way. I'm very appreciated with his answer. His formula works fine, and I used it as a basis for further development:

            1. I've made a 'parameter' which is easily regulated to set a date of data scraping from facebook.

            2. I've made some improvements to transform related date into normal date

            3. Tableau is not an ETL but it's so nice to work in a single shell.

             

            Here is my formula. Don't laugh!

            IF CONTAINS([date], 'Yesterday') THEN

            DATEADD('day',-1,[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Monday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Tuesday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Wednesday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Thursday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Friday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Saturday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Sunday') THEN

            DATEADD('day',-(INT(DATEPART('weekday',[parsedate],'monday' ))-INT( [weekdaynumberextract])-1),[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'Today at') THEN

            DATEADD('day', DATEPART ('day',[parsedate]),(

            DATEADD('month', DATEPART ('month',[parsedate]),(

            DATEADD('year',DATEPART('year',[parsedate]),DATE( DATEPARSE("h:mm", (REPLACE( [date],"Today at ",""))) )))

                                                           )))

            ELSEIF CONTAINS([date], 'hour ago') THEN

            DATEADD('hour',0,[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'hours ago') THEN

            DATEADD('hour',0,[parsedate] )

             

             

            ELSEIF CONTAINS([date], 'minutes ago') THEN

            DATEADD('minute',0,[parsedate])

             

             

            ELSEIF CONTAINS([date], ', 20') THEN

            DATE( DATEPARSE("MMM dd, yyy",[date]) )

             

             

            ELSEIF CONTAINS([date], ' at ') THEN

            DATEADD('year',DATEPART('year',[parsedate]),DATE( DATEPARSE("MMM dd h:mm", (REPLACE( [date]," at",""))) ))

             

             

             

             

            ELSE

            DATEADD('year',DATEPART('year',[parsedate]),DATE(DATEPARSE("MMM dd, yyy",[date]) ))

             

            END

            Now date works fine.

            pic.PNG.png

            • 3. Re: Re: facebook date parse
              Shawn Wallwork

              Alexander, glad you got it all to work. The end results look nice -- certainly shows a pattern, which is afterall what it's all about.

               

              Cheers,

               

              --Shawn