Converting null to zero in "Expression" and "Column Expression" node.

[ajit555]

Hello,
Could you please help in modifying the expression where the null values can be converted to O (zero) in the expression itself (using if … else or anything else) ?

Column-Expressions.knwf (77.0 KB)

How to convert the null value to zero using expression for the below nodes:

“Prev_c1” column, first row, null value should be 0 (zero)

“Prev_c1+c2” column, the first row, the value should be 10 instead of null

How to compute the same (“Prev_c1” & v"Prev_c1+c2" columns) using “Column Expression” node

[JPollet]

Hi @ajit555,
If Prev_c1 is a mathematical result (due to the c1 values special structure) $["c1"]-1 would do the job. But I don’t think it’s the case. If not (ie previous value is necessary), “value should be 0” is an external information so if($["c1",-1]=MISSING,0,$["c1",-1]) seems to do the job in the first Expression node.
Syntax is different in column expression :

if (isMissing(column("c1",-1)))
    {0}
    else
    {column("c1",-1)}

By the way, I just learn that column("c1",-1) works in column expression !! Which is great : thanks for this !

Best,

[mlauber71]

@ajit555 one way to have the first row as 0 would be this:

$["c1",-1] ?? 0

https://docs.knime.com/latest/knime_expressions_guide/index.html#_operators

[mlauber71]

@JPollet

By the way, I just learn that column("c1",-1) works in column expression !! Which is great : thanks for this !

You will have to activate multi-row access

[ajit555]

Column-Expressions.knwf (76.8 KB)

Problem solved after incorporating the above two solutions.

Thank you so much.

[JPollet]

I may suggest a correction for the second line in Column expressions to have exactly the same results including the type of the values :

The two options for the ‘if’ have to be between {}.
Best regards,

[ajit555]

I am very new to syntax coming from Excel background.

Is there an option to define intermediate variables in the expression for “Prev_c1+c2” column ? e.g. i want to define a variable as -

intervar1 = if (isMissing(column(“c1”,-1))) {0} else {column(“c1”,-1)}
out = intervar1 + column(“c2”)

“out” variable value would be the output for column “Prev_c1+c2”

[JPollet]

Yes you could define a intermediate variable. The result “out” is unecessary. See documentation in the description of the node or Column Expressions – KNIME Community Hub
For this node " The syntax and grammar of the expressions are based on the Javascript".

[ajit555]

Thanks. Below is giving Syntax error:

var1 = if (isMissing(column(“c1”,-1))) {0} else {column(“c1”,-1)}
var1 + column(“c2”)

[JPollet]

Try if (isMissing(column(“c1”,-1))) {var1=0} else {var1=column(“c1”,-1)}

[ajit555]

@JPollet

if (isMissing(column(“c1”,-1))) {var1=0} else {var1=column(“c1”,-1)}

Yes it worked.

//if (isMissing(column(“c1”,-1))) {0+ column(“c2”)} else {column(“c1”,-1)+ column(“c2”)}
if (isMissing(column(“c1”,-1))) {var1=0} else {var1=column(“c1”,-1)}
var1+column(“c2”)