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?
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
(column(“MG L07 ID”) == “”)
column(“MG L06 ID”);
(column(“MG L06 ID”) == “”)
column(“MG L05 ID”);
(column(“MG L05 ID”) == “”)
column(“MG L04 ID”);
(column(“MG L04 ID”) == “”)
column(“MG L03 ID”);
(column(“MG L03 ID”) == “”)
column(“MG L02 ID”);
column(“MG L07 ID”);
it has blank values and its a string data type
I put some data together and ran it, here is what’s generated as the output:
Column Expressions node:
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.
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$
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.
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.
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)
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)
i would solve it in a completely different way. See example.
Example.knwf (73.9 KB)
Thanks a ton @JanDuo. This is what i was looking for. Great.
Thanks @morpheus. This method has also worked out. Great
I have done this using Rule engine. Try below-
MISSING $Column A$ AND MISSING $Column B$ AND MISSING $Column C$ =>$Column D$
MISSING $Column A$ AND MISSING $Column B$ =>$Column C$
MISSING $Column A$ =>$Column B$
Thanks @Hrushi for your update.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.