3 Replies Latest reply on Jun 22, 2017 3:25 AM by Mark Fraser

    Function Dateparse not recognized

    Krzysztof Bordzan

      Hi,

       

      I have problem with Dateparse function.

       

      It works on one source (Excel), but when try to use it connecting to same dataset loaded to Google BigQuery function is unknown.

       

      I have Year as number, and Month name as String.

      I'd like to create Date dimension.

       

      Formula created and working on Excel source:

       

      DATE(DATEPARSE("yyyy/MM",STR([Year])+"/"+STR((IF [Month Name] = "January" then 1

      ELSEIF [Month Name] = "February" then 2

      ELSEIF [Month Name] = "March" then 3

      ELSEIF [Month Name] = "April" then 4

      ELSEIF [Month Name] = "May" then 5

      ELSEIF [Month Name] = "June" then 6

      ELSEIF [Month Name] = "July" then 7

      ELSEIF [Month Name] = "August" then 8

      ELSEIF [Month Name] = "September" then 9

      ELSEIF [Month Name] = "October" then 10

      ELSEIF [Month Name] = "November" then 11

      ELSEIF [Month Name] = "December" then 12

       

      END))))

       

      If I will remove Dateparse, keep only

      STR([Year])+"/"+STR((IF [Month Name] = "January" then 01

      ELSEIF [Month Name] = "February" then 02

      ELSEIF [Month Name] = "March" then 03

      ELSEIF [Month Name] = "April" then 04

      ELSEIF [Month Name] = "May" then 05

      ELSEIF [Month Name] = "June" then 06

      ELSEIF [Month Name] = "July" then 07

      ELSEIF [Month Name] = "August" then 08

      ELSEIF [Month Name] = "September" then 09

      ELSEIF [Month Name] = "October" then 10

      ELSEIF [Month Name] = "November" then 11

      ELSEIF [Month Name] = "December" then 12

       

      END))

       

      and will try to convert type to Date, after adding to viz, Google BQ says that '2015/5' is invalid date.

       

      Any reason why Dateparse do not work?

      Is there a way to have workaround?