Improvement of a workflow on a data processing

[stef_pf]

I was able to get my result, except that I think there is another way to do it, using fewer nodes like the expression column in which there are formulas. There is probably another way to use the pivot node.
I’m open to suggestions if you give me some ideas.
Thanking in advance, the person who will answer me.
Processing _conf_lte.knwf (102.0 KB)

[kevin_sturm]

Hello @stef_pf,

And sorry for the late reply. As you might know already, there are a lot of different ways to get to your result and there are probably multiple ones which can be treated as the best way to do so. In fact, you are already doing quite good while using <pivoting>, <rule engine>, and <column expression> nodes.

Surely, there is the option to use Metanodes or even Components to clean up your top-level workflow and encapsulate meaningful sections and even add configuration or interaction capabilities (to Components).

I saw you were using a single <interactive table (local)> node at the end. Would you be able to explain your idea behind it? To allow even more functionality, you could use a <table view/editor> node instead (which could also be put into a Component.

There is one last remark for now, within the last section of your workflow, you are using three <string manipulation> nodes consecutively. All of them are connected via a data table connection and a flow variable connection. In KNIME, one a flow variable is created by a node, it will also flow via the generic data table connection to the next node. Thus, you do not need to connect the nodes via the flow variable connection as well.

Best regards,
Kevin

[stef_pf]

Hello Kevin,

Thank you for your reply.

For your question about the table at the end of the workflow, it only serves as a result.

I would like to avoid using If / else formulas in the node column expression.
For example here, I use the node missing value to replace an empty field in a cell with “n/a”, so that in the node expression this condition is taken into account.

There may be another way to achieve the result by using a loop with a variable.

I’m just looking for a better way to avoid using formulas. I tried another way, but here I get for example this result: 1/1/? or 1/ instead of having this 1/1 and 1.

With a formula no problem.

Looking forward to reading you, have a good weekend.

[bruno29a]

Hi @stef_pf , is there any reason why you need to convert empty/missing values to “n/a” to right away change it to “”? Instead, why don’t you:
either: Change the missing values of these columns to “” in the Missing Value node
or: Check for missing values of these columns directly in the Column Expression

“I would like to avoid using If / else formulas in the node column expression.” There’s a trick that you can use here. Knime will not do String manipulation on missing values, so join() or joinSep() on columns that have missing values will result in missing values.

With that in mind, you can just do the joinSep() via a String Manipulation, meaning with no rules (no if/else). Knime will automatically apply the rule about the missing values, so column “1” with missing values will stay missing, but those with some values will joinSep() properly (that is to say that the same rule applies to all columns, including the Type Radio B20).

You can then replace the missing values via the Missing Value node after. Here’s a very simple demo:

Input sample:

The String Manipulation basically does the same operation that you are doing:

joinSep(" ", $1$, $Type Radio B20$)

Results of the manipulation:

As you can see, those rows that had missing values in column 1 stayed as missing values.

Then just replace the missing values with empty string via the Missing Value, and we get this:

This was done without having to implement any logic or if/else.

[stef_pf]

Hi bruno29a,

Thank you, this méthod is the result I want.

Any idea, for this section?

BR, stef.pf

[bruno29a]

Hi @stef_pf , I’m assuming you are asking if this can be done without if/else?

It certainly can. You can do it via the Rule Engine, though you won’t use if/else, you still need to implement the same logic. Just not with if/else:

$L1$ = 1 AND $L2$ = 1 AND $L3$ = 1 => "1/1/1"
$L1$ = 1 AND $L2$ = 1 => "1/1"
$L1$ = 1 => "1"
TRUE => ""

Input:

Results:

Knime is most certainly interpreting the rules as if/else in the back-end.

It can also be done without implementing any logic, and let Knime handle the missing values by itself.

You can do this by creating a Collection made up of these 3 columns and then just split the collection by gluing the elements with “/”.

First, we need to convert the columns from number to string as we want to do some string manipulation in the end. We can do that with the Number To String node:

We then create a Collection out of the 3 columns. The trick here is to ignore the missing values:

This is how the collections look like:

Now just joinSep() the collection using the String Manipulation:
joinSep("/", $lte_800$)

Results (same as with Rule Engine):

In this case, you don’t have to implement any logic.

This is how the workflow looks like (with both methods):

[stef_pf]

Hi bruno29a, thank you for your answer, I have chosen the second solution, which seems to me more adapted.

Here, do you think it is possible to use only one pivot node?

BR. Stef

[Daniel_Weikert]

I would assume the column aggregator node can give you this result as well
br

[stef_pf]

Hi all,
Hi everyone, thanks for your answers, I managed to optimize my workflow without using if/else formula and using only one pivot node.
Processing _conf_lte.knwf (97.4 KB)
BR.