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.

13replies 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.

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

     

    Let me know if helps in your case. 

    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?

    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

    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.

    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.

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

      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.

    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

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

    Like
    • Felecsia Ford Not sure this will work for your specific issues, but recently I had a similar issue and used the following (we were replacing all 1/1/1769 dates with null):

       

      replace(

       formatDate(

       Backlog.Date,

       "MM/dd/yyyy"

       ),

       "1/1/1769",

       "Null"

      )

      Like
    • Tom Wilseck Thanks for your help... but the suggestion from Dustin works, and I prefer to render a blank space instead of the word 'null'.

      Like
    • Felecsia Ford please refer to https://community.incorta.com/t/y7js4h and see if the property is still there in the server file .

      Like
Like Follow
  • 1 mth agoLast active
  • 13Replies
  • 302Views
  • 9 Following

Product Announcement

Incorta 5 is now Generally Available