Conditional Column

Hi All,

I’m a Power Query user, but I’m touching the limit because of very big database.
So I’m trying to use knime instead., so sorry if my question is very basic., I’m still a beginner.
With a joiner I test several mapping-matching scenario, it returns me somthing like that.

Lookup¨¨¨¨¨¨|¨¨Join1¨¨|¨¨Join2¨¨|¨¨Join3¨¨|
Unknown1¨¨¨|¨¨¨¨?¨¨¨¨¨|¨¨¨¨?¨¨¨¨¨|¨¨dog¨¨¨|
Unknown2¨¨¨|¨¨¨¨?¨¨¨¨¨|¨¨¨cat¨¨¨¨|¨¨¨¨?¨¨¨|

at the end I would like to do someting like this:
if Join3 is Missing (=?) then Join2 but if Join2 is missing then Join1 but if Join1 is missing “unknown”.
In Power query it would have been someting like than:
If [Join3]<>null then [Join3] else if [Join2]<>null then [Join2] else if [Join1]<>null then [Join1] else “unknown”
I don’t think I can use a merge column because I need a hierarchy in column Join3>Join2>Join1>“Default text”

I think I’ve to do it via a rule engine, but I don’t know exactly the syntax to write it.

Can you help me ?
Regards,
Laurent

Hi,

Answering myself, I think I find the syntax in the rule engine:
NOT MISSING $Join3$=>$Join3$
NOT MISSING $Join2$=>$Join2$
NOT MISSING $Join1$=>$Join1$
TRUE=>“unknown”

Larent

1 Like

You can append a default “unknown” value and apply Coolumn Aggregator.
Try this:
KNIME_forum_23093_pigreco.knwf (7.9 KB)

In this way you have not any limit in number of columns, The workflow is valid always.

2 Likes

Hi there,

like you solution @pigreco only would first apply Column Aggregator followed by Rule Engine or Missing Value node to replace possible missing value with “unknown”. Not fond of extra columns which at some point require Column Filter node :smiley:

Br,
Ivan

Why do you want first apply Column Aggregator?
Missing Value node is more complex than fixed value column.
Which part of your scenario is not solved by my workflow?

Hi @pigreco,

would first apply Column Aggregator in order to avoid adding additional column to data set and then need to use one more node (Column Filter) to remove it. Yes, Missing value is a bit more complex (Rule Engine is not) and your workflow should solve every scenario. Was adding my approach…

Br,
Ivan

1 Like

Ok. If you want you can use Java snippet to test missing value and substitute it.
Rule engine is also a good idea.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.