Need help replicating an excel formula in KNIME. I’ve include a sample excel file with dummy data for reference.
Essentially I need create a column that increments 1 every row where the previous attribute value is the same as the current one. Once the attribute has a new value, I need to grab a new value to start again. I already have my dataset structured and sorted appropriately.
Any help appreciated!
Knime Example.xlsx (9.2 KB)
I would use a Lag Column node for both your attribute and value columns, then all of the necessary values are on the same row for you to do an if statement to compare the attributes. If the Lag and original attributes are different then the new value = the old value, else the lag and old attributes are the same and the new value = the lag value +1.
@iCFO Thanks so much. Using 2 Lag Column nodes in a row caused an “Encountered Duplicate Row ID” error, but I was able to work around that by combined my attribute and metric columns, lagging the combined column, and using a Cell Splitter to split the lagged column.
What node would you recommend to implement an IF statement? The Math Formula node only numbers, so I can bring in my string attribute in the IF statement.
I would use Rule engine or Column Expressions. Let me know if you have trouble with the syntax for the if statement.
Most likely one of those lag columns was the Row ID. You can use this node to duplicate the Row ID to a new column.
@iCFO Good note on the RowID node tip. Thanks!
I am struggling with the IF syntax since the calculation uses it’s previous value to increment when the last attribute equals the current attribute.
Your help is SO appreciated!
The Lag column drops those prior row values to the same row, so it should be as easy as referencing those lag columns in the statement rather than using a fancy multi row formula.
Renaming those lag columns to something like Prior Row Attribute and Prior Row Value might help.
I will try to upload a demo workflow for you and I when I get back to the office.
@iCFO I’ll keep trying but right now I don’t see the way.
An example workflow would be awesome if you have the time.
@jcallaway I calculated using the rank node, which is an easier approach. Take a look. It is a neat tool for doing grouped running counts.
Sample Workflow.knwf (40.3 KB)
@iCFO Very cool method! Walked through your sample workflow. This should apply perfectly.
Thank you so much!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.