11 Replies Latest reply on Sep 8, 2016 4:01 AM by kettan

    DATEPARSE funtion + GoogleSheets [Tableau Public]

    Marcin Tymczak

      Hi Guys!

       

      I have a data set in Google Sheets with a string field [EventDate] containing dates in this format "28-Oct-2016". I was trying to DATEPARSE it, but i am getting null values, same situation happends with DATE  function. Any ideas?

       

      Syntax I use:

       

      DATEPARSE("dd-mmm-yyyy",[EventDate])

      DATE([EventDate]

       

      Cheers,

       

      Marcn

        • 1. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
          Tom W

          Please share some sample data in a Tableau Packaged Workbook so we can replicate.

          • 2. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
            Mahfooj Khan

            Try this,

            DATEPARSE("dd-MMM-yyyy",[Date])

            Mahfooj

            • 3. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
              Marcin Tymczak

              .twbx attached

               

              Mahfooj Khan it worked partially, the outcome is quite strange though: the dates with 2014 have been only converted to datetime format, the rest returned null.

              • 4. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                kettan

                As already shown by @Mahfooj, mmm must be uppercase MMM, because "m" is for minutes and "M" is for months as previously explained here:  Re: Convert MMM-YY string to date

                 

                Although not an issue in your case, yyyy probably have to be lowercase as previously explained here:   Re: How to parse date in format "MMM-YY" , often this format comes from legacy ex., JAN-98 for JAN-1998.

                 

                I have sometimes had issues with Excel not recognizing Oct and Dec as dates when Windows locale is in my locale language with October and December spelled Oktober and Desember. I believe you may also experience this if your language isn't English. I just gif recorded this in LibreOffice Calc 5.1:

                 

                thread 214221 DATEPARSE funtion + GoogleSheets [Tableau Public].gif

                 

                If this is the case, I think you can set your data source to any English language locale.

                That is, you may not need to change your Windows locale to help Tableau to recognize English dates.

                 

                Ps. Off-topic:

                 

                I like the farmer/nature/season related month names in Polish and wish we also had something similar:

                 

                Now, let me focus on the meaning of the months in Polish language, tradition and history.

                The months names are related to the farmers calendar through the year.

                 

                1. Styczeń - stykac means "to meet" - "to join" since he old year "meets: the new year in January
                2. Luty - luty in old Polish means "fierce", "bitter frost", "freezing cold"
                3. Marzec - from marznac meaning " to freeze". Some people believe that this name comes also from Roman God "Martius" - in Polish Mars.
                4. Kwiecień - from kwiecie - flowers - a blooming month.
                5. Maj - this is the only name which was adopted from Roman calendar devoted to goddess Maia. Maia was the Roman female deity of growth or fertility.This name gained its own meaning in the Polish language, for instance as majowka- the outdoor trip.
                6. Czerwiec - from czerw meaning "grab" - the larve of a bee or moth. In this month people were picking up the larves dry them in the sun and then made from it a red or purple die (pigment); therefore the name "czerw" comes from "redden" and some sources also derive the name of Polish June from a Polish word "czerwienic" - to redden or rippen
                7. Lipiec - from lipa - "linden tree" - which flowers in that time; this tree is very popular in Poland. There is a famous poem by Kochanowski "Na lipe" (about Linden tree) - "gosciu siadz pod ma lipa a odpoczyn sobie" - "dear visitor -please sit under my linden tree and relax" . Read Love and Lore of the Linden
                8. Sierpień - sierp is "a sickle" used for harvesting (tool to cut the hay, grass or wheat)
                9. Wrzesień - from wrzosy - "heather" that beautifully purple in that time of the year
                10. Październik - from paździerz - tow, wooden dry part remained from flax or hemp. In the past flax and hemp was used for making cloths. In that time of the year the wind was carrying out "paździerze" - the wooden waste remained after flax and hemp - all over the fields.
                11. Listopad - padajace liscie - falling leaves.
                12. Grudzień - gruda - hardened ground which is caused by cold weather.

                 

                Source:  Months in Polish calendar - origin and meaning - Polish Culture

                 

                Ps. Farther off topic:

                 

                I am currently (self)studying my own native language, Faroese, a Germanic language. Although linked article mentions our roots as North Germanic, I think that I have read something about Eastern Germanic tribes moving to Scandinavia and that these might have been the people that fled from Norway to Faroe Islands. This is from memory and therefore quite possibly wrong! ... but if true, it means that some ancestors of the Faroese people once upon a time lived in today's Poland!

                 

                Why do I mention this? Because my eyes fell on ...

                 

                1. Grudzień - gruda - hardened ground which is caused by cold weather.

                 

                ... and got some associations in Faroese that fitted this meaning so well that I started to believe there is a connection! Thereafter I thought, maybe gru is the key for words with similar meaning and looked up words in Faroese-Faroese and Faroese-English  dictionaries and was amazed how many words beginning with gru fitted gruda's meaning, although only mentioning a few here:

                 

                ground

                grund - ground (land)

                grunnur - ground (sea), shallow (water)

                 

                hard and cold (treatment)

                grummur - cruel  ( grummur maður - cruel man )

                grussa - angry, biting woman

                 

                But of course, these might fit North Germanic just as well.

                For example does Danish have similar words with similar meanings:

                 

                grund - ground

                grusom - cruel

                • 5. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                  Marcin Tymczak

                  kettan I changed the locale, bu still nothing. I cant understand, how it's possible that only some of the dates are being converted and some not...

                   

                  EDIT: To be sure i don't have any trailing spaces I also used RTRIM(EventDate), but didn't help.

                  • 6. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                    kettan

                    AFTERTHOUGHT

                     

                    Could you try to format your date column in Google Sheet as e.g. yyyy-mm-dd and see if all change?

                    Those who do are dates, those who don't are strings giving you problems in Tableau.

                    Correct these and issue might be solved without the use of DATEPARSE().

                     

                    Ps. I have only used Google Sheets for a short time 7 years ago, but assume you can format dates there.

                    • 7. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                      kettan

                      A little bit late I saw that you have shared a packaged workbook and therefore deleted previous comment.

                       

                      kettan I changed the locale, bu still nothing.  I cant understand, how it's possible that only some of the dates are being converted and some not...

                      Could you share a screenshot of Region and Language setting as below?

                       

                       

                      I set mine to Polish (Poland) and managed to make the formula work by translating each and every month to Polish! Since most of your months worked, I assume your language setting isn't Polish, though, and neither English!  (I didn't need to include March since the first three letters are the same in English and Polish. I included it anyway for easier reading and copying to another language which I have a feeling your Windows is set to use.)

                       

                      DATE( DATEPARSE( "*dd MMM*yyyy",

                      replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace([date],

                      "Jan","sty"),

                      "Feb","lut"),

                      "Mar","mar"),

                      "Apr","kwi"),

                      "May","maj"),

                      "Jun","cze"),

                      "Jul","lip"),

                      "Aug","sie"),

                      "Sep","wrz"),

                      "Oct","paź"),

                      "Nov","lis"),

                      "Dec","gru")

                      + STR([year]) ) )

                       

                      I don't have a license to Tableau 10.0 and therefore did the test above with Tableau 9.0 Desktop Personal.

                      (I am not a user of Tableau Public)

                       

                      Attached Workbook Version: 9.0 (modified XML from 10.0 to 9.0)

                      • 8. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                        Marcin Tymczak

                        That was pretty cool, kettan it worked indeed! Thanks a lot! Although i don't really understand why it happens, I changed the workbook locale to English and it didn't help.

                        I assume, if a Korean user would like to do the same thing i wanted to do, he should replace all the names of the months accordingly

                        • 9. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                          kettan

                          Glad to hear 

                           

                          Although i don't really understand why it happens, ...

                          I found an explanation about it here:   Understanding the DATEPARSE Function | Tableau Software

                           

                          For text files, we can change locale language on data source and wonder if this would affect DATEPARSE. But seemingly, we can't do that for Excel   and this would therefore not make any difference in your case.

                           

                          ... I changed the workbook locale to English and it didn't help.

                          Strange. I just set my format to English (United States) and voila, this formula worked for all incl. May, Oct and Dec:

                           

                          DATEPARSE( "*dd MMM*yyyy", [date] + STR([year]) )

                           

                           

                          I assume, if a Korean user would like to do the same thing i wanted to do, he should replace all the names of the months accordingly

                          Yes, possibly      ... but if this user shares the workbook with someone with a different language, there will be issues 

                          • 10. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                            kettan

                            It would of course make the formula independent of language if month is replaced with a number:

                             

                            DATE( DATEPARSE( "*dd MM*yyyy",

                            replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( [date],

                            "Jan" , "01" ),

                            "Feb" , "02" ),

                            "Mar" , "03" ),

                            "Apr" , "04" ),

                            "May" , "05" ),

                            "Jun" , "06" ),

                            "Jul" , "07" ),

                            "Aug" , "08" ),

                            "Sep" , "09" ),

                            "Oct" , "10" ),

                            "Nov" , "11" ),

                            "Dec" , "12" )

                            + STR([year]) ) )

                             

                            The reason I came to think about this is because I just answered a similar question:

                             

                            Re: Dateparse not working for me

                            • 11. Re: DATEPARSE funtion + GoogleSheets [Tableau Public]
                              kettan

                              Here is a calculation that might perform better and perhaps also easier to read and use:

                               

                              DATE( DATEPARSE( "*ddMMyyyy",

                              LEFT( [date], 3 ) +

                              CASE MID( [date], 5, 3 )

                              WHEN "Jan" THEN "01"

                              WHEN "Feb" THEN "02"

                              WHEN "Mar" THEN "03"

                              WHEN "Apr" THEN "04"

                              WHEN "May" THEN "05"

                              WHEN "Jun" THEN "06"

                              WHEN "Jul" THEN "07"

                              WHEN "Aug" THEN "08"

                              WHEN "Sep" THEN "09"

                              WHEN "Oct" THEN "10"

                              WHEN "Nov" THEN "11"

                              WHEN "Dec" THEN "12"

                              END +

                              STR( [year] )

                              ) )

                               

                              Ps. I made this kind of calculations more "authorized" by saving them in a document in the The specified item was not found., namely DATEPARSE foreign month names