how to get the loop index as a new column for a string column

I have string column named “PYR”. I want to get a new column which has the count occurrence as a reducing numbers based on the date with the latest date having the highest number from -1 to -100. (the yellow column)

sss

Hi @pyroo56323 , and welcome to the KNIME community!

This task can be achieved using the Rank node to work out the sequence within groups (ordered by descending date within PYR groups), and since you require it to be negative values, a math formula node can multiply the result by -1

FIrst off though, your dates need to be converted to “real” dates as currently they are in String format. This is achieved using the String to Date&Time node.

Here is an example flow. At the end, I tidy up using a Column Filter to keep only the columns you have shown. However, you may of course choose to retain the converted Date column too, but that depends on your subsequent use case.

image
Counting within groups.knwf (13.4 KB)

5 Likes

worked like charm thank you

1 Like

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