Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

victor_greffet
New Contributor III

blank field

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 ?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: blank field

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.

2 Replies
MVP
MVP

Re: blank field

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.

mark6505
Valued Contributor III

Re: blank field

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

Community Browser