Column Expression Node Help Required

HI,

I want to use nested if in Column expression node for below example.

if Column A is blank then Column B
else
if Column B is blank then Column C
else
if Column C is blank then Column D
else
if Column D is blank then Column E
else
Column A

Kindly help me with the syntax to be used in KNIME Column Expression node.

See hee
https://www.tutorialspoint.com/java/nested_if_statements_in_java.htm

Thanks for your reply.

I am looking at below.

If in Column A any row is blank then consider Column B for that row
else
If in Column B any row is blank then consider Column C for that row
else
If in Column C any row is blank then consider Column D for that row
else
Column A

Also can i do this with rule engine node

Hey @JatinShah based on your original post here is how you can write the expression in the Column Expressions node:

if
(column("Column A") == "")
    column("Column B");
else if
(column("Column B") == "")
    column("Column C");
else if
(column("Column C") == "")
    column("Column D");
else if
(column("Column D") == "")
    column("Column E");
else
    column("Column A"); 

Don’t forget to check for Missing (?) cells that you may want to account for.

I hope this helps

1 Like

its not working. any other solution?

Hey @JatinShah,

When you say it’s not working are you writing out to a new column for the results of the expression in the right data type (string, integer etc)? Is it also possible that Column E needs to be accounted for in your logic? Also, do you have any Missing values, rather than blank values?

As they say, if you have an example or sample of your data to show that could really help in troubleshooting your issue.

Please check below

if
(column(“MG L07 ID”) == “”)
column(“MG L06 ID”);
else if
(column(“MG L06 ID”) == “”)
column(“MG L05 ID”);
else if
(column(“MG L05 ID”) == “”)
column(“MG L04 ID”);
else if
(column(“MG L04 ID”) == “”)
column(“MG L03 ID”);
else if
(column(“MG L03 ID”) == “”)
column(“MG L02 ID”);
else
column(“MG L07 ID”);

it has blank values and its a string data type

Hey @JatinShah,

I put some data together and ran it, here is what’s generated as the output:

Original:
image

Column Expressions node:

Final Output:

Is this what you’re trying to do? new column0 contains the value from Column L05 because L06 was blank.

Note Sometimes when you copy and paste expressions you need to fix quotation marks for it to work.

Hey thanks for your update.
I am looking at something else

For example, I have 100 records,
in 1st record value is in MG L07 ID to MG L02 ID,
2nd record data is in MG L06 ID to MG L02 ID --(L7 is blank),
3rd records data is in MG L05 ID to MG L02 ID–(L7 & L6 is blank), so on

So i need output
1st Record it should capture L7
2nd record L6
3rd record L5
and so on.

Anyone kindly guide with the details.

Hi @JatinShah,
I’m trying to understand the rational behind your nested if expression. Are you looking for the first non blank column starting from A all the way upto E?
Because that’s not how I would interpret your expression, which is the same as how @TardisPilot coded it in the solution.

If you want the first non blank column you could indeed use a rule engine node for this too. It would look like this, where the first row which is gives a TRUE will be used (and the rest is skipped).

NOT MISSING $ColumnA$ AND NOT $ColumnA$ = "" =>  $ColumnA$
NOT MISSING $ColumnB$ AND NOT $ColumnB$ = "" =>  $ColumnB$
NOT MISSING $ColumnC$ AND NOT $ColumnC$ = "" =>  $ColumnC$
NOT MISSING $ColumnD$ AND NOT $ColumnD$ = "" =>  $ColumnD$
TRUE => $ColumnE$

Hi @JanDuo,
I need to check in case as per below scenario
1st Record - If value is missing in MG L7, then consider L6.
If value missing in L7 & L6, then consider L5.
If value missing in L7,L6 & L5, then consider L4.
If value missing in L7, L6,L5 & L4 then consider L3.
If value missing in L7, L6,L5,L4 & L3 then consider L2.
If value missing in L7, L6,L5,L4, L3 & L2 then consider L1.
and so on

Kindly let me know if any further details required.

@JatinShah what do you mean by “consider”? This sounds a bit like you start a new “if-condition”.

From this reply of you I get the impression you want the first non blank column

If you use a rule engine node and apply the mentioned logic that will give you the requested result. Just try it and see it for yourself.

Hi @JanDuo,
thanks will try.

Just to clear my requirement.

For each record, I have 8 columns. 1st column is Global ID and rest 7 columns has value
So for each record it should give the output from the 7 columns, i.e out of 7 columns which column has the last data.

Eg:

Global ID L1 L2 L3 L4 L5 L6 L7 Output
123 1 2 3 4 5 6 7 7
456 2 3 5 6 7 8 8
789 5 2 8 6 4 4

Hope this clarifies.

Sample.xlsx (13.3 KB)

Hi @JatinShah

Very small workflow:
afbeelding

Adding one column to your data based on the result of the rule engine:

This is the workflow, using your example data.
26235.knwf (20.4 KB)

1 Like

Hi @JatinShah,

i would solve it in a completely different way. See example.

Example

BR

Example.knwf (73.9 KB)

2 Likes

Thanks a ton @JanDuo. This is what i was looking for. Great.

1 Like

Thanks @morpheus. This method has also worked out. Great