Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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.
Hi,
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.
Mark