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.
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
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:
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.
Hi @JatinShah
Very small workflow:
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)
Hi @JatinShah,
i would solve it in a completely different way. See example.
BR
Example.knwf (73.9 KB)
