0

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:

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"),?)

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.

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Dan,

    As far as I know, we cannot populate null value inside the formula column in Incorta, but there is a workaround.

    You can populate a null column from your source table "rtn_item" and use it inside your formula column.

    Reply Like
  • Please try "null()". Do not miss the parentheses.

     

    Let me know if helps in your case. 

    Reply Like
  • 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?

    Reply Like
  • 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: 

    RowID,NullDate,NullString,NullLong
    1,,,

    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?

    Thanks,
    Dustin

    Reply Like
  • 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.

    Reply Like
  • 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.

    Reply Like
    •  It should short circuit the ELSE. Definitely worth evaluating in your dev environment for impact.

      Reply Like
  • In our case i created a external session variable (with the name Null for easy use) and put the below select query in the query section.

    select Null from dual

    you can later reference this variable in the formulas.

    Reply Like 1
  • 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"))

    Try this

    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

    Reply Like
Like Follow
  • 1 mth agoLast active
  • 9Replies
  • 71Views
  • 6 Following