NULL date value in formula
I'm trying to add a formula column on a table that will convert a date string to a date. The string is always formatted "mm/dd/yyyy" *but* there are some rows that have "00/00/0000" or "01/01/1900" in them. I want these to be NULL, while any other values will return the date associated with the string.
Here's my formula:
What do I use in the else condition to specify that I want a NULL value returned? I don't see an obvious way to specify a NULL value in a formula like this.
Thanks for the replies. I did try null() originally, but it generated some unexpected errors/problems. I can't remember the exact error message, but I would get an error when trying to use the table in a dashboard, or in some cases I wouldn't get any results at all. As soon as I removed the null() from the formula, everything returned to normal.
I will try the other workaround, although this source table is 300M+ rows and doing a full load on the table to add the null column will take time. Also, I'm assuming I need to cast the null to a date data type for it to work in the formula column, correct?
Hi Dan Nielsen ,
We don't support NULL directly as a formula return type (yet), but we may have a workaround. I created a new table from a csv. The csv looks like this:
And then I tested a formula column that uses a lookup:
lookup(Sales_Hierarchy.NullTable.NullDate, Sales_Hierarchy.NullTable.RowID, 1)
Can you test this for your use case and let us know if it works?
I will give it a try. But I'm concerned about performance with this approach since Incorta would be calling the lookup() function for every row in the table. That's currently 300M+ rows today, and it could grow much larger in the future.
Since we've had instances where formula column calculations have taken several hours to complete when doing a schema refresh, I'm a little wary of this approach but will try it out and see.
Hang on... If my full formula looks something like this:
if(not(in(RxReturns_Stage.rtn_item.expir_date,"00/00/0000","01/01/1900")),parseDate(RxReturns_Stage.rtn_item.expir_date,"mm/dd/yyyy"),lookup(RxReturns_Stage.NullTable.NullDate, RxReturns_Stage.NullTable.RowID, 1))
Will the lookup() function get called for every row, or only for rows that hit the ELSE condition? If it's only executed when a row hits the else condition, it might not be too bad.
if(or( parseDate(RxReturns_Stage.rtn_item.expir_date,"MM/dd/yyyy") = parseDate("00/00/0000", "MM/dd/yyyy"), parseDate(RxReturns_Stage.rtn_item.expir_date, "MM/dd/yyyy") =parseDate("01/01/1900","MM/dd/yyyy")=true), null(), parseDate(RxReturns_Stage.rtn_item.expir_date,"MM/dd/yyyy"))
if it is 00/00/0000 or 01/01/1900, will get an empty space
if it is not 00/00/0000 or 01/01/1900, will get the date type value