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 ?

[jonathandienst · Accepted solution]

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.

[Mark_Little]

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