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.
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.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
