2 Replies Latest reply: Nov 8, 2017 3:36 AM by Mark Little RSS

    blank field

    Victor GREFFET

      Hi community,


      In my app, I have 2 columns with two type of date [DS_Forecast_Date] and [DS_Need_Date]


      I'd like to create a third column which makes the difference between these two dates but if one of this two columns is 'empty' then put zero, so I wrote in the script :


      if(len(trim([DS Forecast Date]))=0,'empty',date([DS Forecast Date])) as [DS_Forecast_Date] ,

      if(len(trim([DS Need Date]))=0,'empty',date([DS Need Date])) as [DS_Need_Date],

      if([DS Need Date]='empty' or [DS Forecast Date]='empty','0',[DS Need Date]-[DS Forecast Date]) as [Need-Forcast]

      But  returned column [Need-Forecast] has blank instead of zero.


      I hope it's clear...

      Do you have and idea ?

        • Re: blank field
          Jonathan Dienst

          If these three lines are all in the load script, then the updated value of [DS_Need_Date] and [DS_Forecast_Date] are not available when line 3 is executed, so the condition always evaluates to false. If either field is null, empty, or non-numeric, this expression will return null and will never return zero. A simpler way would be


          Alt([DS Need Date]-[DS Forecast Date], 0) as [Need-Forecast]  // note field name spelling

          This assumes that the two date fields are numeric data values and not strings.

          • Re: blank field
            Mark Little



            I would try turning them to numbers and check for a result.

            So change the end to NUM([DS Need Date])-NUM([DS Forecast Date])) as [Need-Forcast]

            If that works, then wrap that back into a date() funtion.