I am trying to build an if statement with knime, but I am not sure how. I need to first check if two IDs are the same, if they are, then check on a certain status and copy the remaining columns. Any ideas on how to do that? Example with some table:
I would use the Pivoting node and postprocess the result later, but in case you really want to use the Rule Engine nodes, you can use them together with the recursive loop nodes (though for me it is unclear how you would like to combine the information, maybe a Joiner node (self join) with postprocessing would be a better fit).
Is there a logic to determine which status should to be retained from each ID? I was thinking you can use a Cell Replacer node to associate an ordinal to each status, then sort by ID, finally go through a Group Loop to select within each ID the maximum/minimum status to retain.
Using a Rule Engine is unnecessary as long as the status to ordinal mapping represents the natural sorting of your Statuses. See also the attached workflow.
First you have to have a table specifying the priority between the different Old status. There are four status values in your example, fixed, closed, oepn and retesting, I've assumed from your example they are ranked accordingly. ie, fixed is preferred to closed, which is preferred to open, and which in turn is preferred to retesting.
Create a table of thsi with the priority ranking as an integer. I've chose smaller integer to be higher priority. Join this to the input table to create a numeric ranking column (Old status priority in the attached workflow), then sort by ID, and the by Old status prioirty. This will make sure that the row you want is the top of each ID group.
Finally, use a GroupBy node and group by the ID column and pick First as the aggregation method. This will select the row ranked according to the assigned priority for each ID group.