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
Null() worked like a charm with v4.7.7. Now, that we've upgraded our development environment to v5.0.4, it no longer works. We're prepping our production environment for the upgrade, and all of the failed formulas in the dashboards that I own are because of the use of null(). Changing the ELSE to '' works for the string data types, but not dates. Dan I see the null table you created based on the suggestion from Dustin . Did that work for you?