Restarting Rank

KNIME Analytics Platform
1

Not sure how to describe the method/outcome, but what I need would be to generate a table like below. So let’s say we have a date column and a value with it, I’d like to have a ‘Rank’ that restarts every time the new row is different from the previous row. Any ideas would be greatly appreciated.

I’ve tried the rank node and looping with ‘Moving Aggregation’, but no luck so far.

Date Value Rank
1/1/18 Low 1
1/2/18 High 1
1/3/18 Medium 1
1/4/18 Medium 2
1/5/18 Medium 3
1/6/18 Low 1
1/7/18 Low 2
1/8/18 High 1
1/9/18 High 2
1/10/18 Medium 1
1/11/18 High 1
2

Quite sophisticated and probably there are much simpler solutions, but the attached WF should do what you’re after.

33
33952×153 10.6 KB

Idea:

  1. Use the “Lag” + “Rule Engine” + “Missing Value” nodes to determine, whether the current row changes its value compared with the previous one and to create groups of consecutive rows

  2. Use a “Group Loop Start” with some inner nodes to do the numbering for each group

  3. Remove helper columns using the “Column Filter”

HTH,
Philipp

RankRestarting.knwf (11.4 KB)

3

qqilihq, you’re a legend. Thank you!