IF with internal Count IF per cell

Hi team!

I have a task that is easily solved through Excel, but I can’t figure out how to do it through Knime.
Here is the list of orders. As we can see, there are duplicates that should remain. I made the Countif column for clarity using the COUNTIF(A:A;A2) formula. This column is not needed. We need the Result column, which is calculated by the formula IF(COUNTIF($A$1:A2;A2)>1;0;1).

In fact, the task turns into a line-by-line expansion of the range of orders with a count of their number for the current sample, assigning the first value to 1 and the remaining 0 if they are repeated.

example

1 Like

Hi @badorak & welcome to the KNIME forum community

This can be easily implemented using a -Lag Column- node on the “Order” Column.

Could you please post your data as a “copy and paste” on the post or as a text file ? It will help us to directly reply to your question with a solution based on your data. It saves us the time to enter the data by hand :wink:

Looking forward.

Best
Ael

2 Likes

Thank you for such a warm welcome! :grinning:
Lag Column.knwf (6.1 KB)

1 Like

Hi @badorak

Thank you for your prompt reply :grinning:

A possible solution is as follows:

The workflow can be downloaded from here:

20221229 Pikairos Detect and Tag Duplicated Cell Values in Column.knwf (28.3 KB)

Hope it helps.

Best
Ael

3 Likes

Hi @aworker

A perfectly simple and elegant solution. Tested on a large array - it works fine. :upside_down_face:
I also wanted to ask why there is an “Interactive Table(local)” node at the end?

Hi @badorak

Thank you for your feedback.

The -Interactive Table (local)- node is a very simple though useful node too. At a first glance, it looks like just a node for visualizing tables, the same as the output view of any node. However, it has the advantage of also allowing to hold on top the table view, which is not always available on the manipulation nodes:

Once the option “Always on top” is checked, one can continue to work on the workflow or run it whilst the table remains on top. This makes easier the visualization of results on the fly while the workflow is running or when one uses the hiliting options of nodes.

Hope it helps too.

Thanks a lot for having validated the solution :grinning:

Enjoy KNIME and best wishes,
Ael

5 Likes

That Interactive Table (local) node with “always on top” selected is a real find! Thanks @aworker !

1 Like

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