Sequential Occurrence Value of a Column

Hi,

I'm looking to append a column to a table which has the "sequential occurrence value", working downwards from the "Value" column as follows:

Value Sequentual Occurence Value
A 1
C 1
B 1
A 2
A 3
C 2
B 2
A 4

Any pointers would be greatly appreciated.

Well, this is one of those times where the easiest solution is to use the Java Snippet node. I generally hesitate to provide solutions to others using the Java Snippet node because many times customers aren't Java users, and I prefer to show them it can be done without using Java. Perhaps there's a clever way to avoid the use of Java Snippet, but I don't have enough time to think of it today.

Please see the attached workflow. In a nutshell, the Snippet declares a 'global' variable that keeps track of the current observed count for each value in a data structure called a Map. For each row, the map is consulted for the last observed count for the value in that row and then the new 'Sequential Occurrence' column is populated with the count value plus 1.

By the way, if it's okay instead to summarize the count by value (i.e. each value gets its own row), you should check out the GroupBy node.

Cheers
Don

Thanks very much Don! That's works perfectly. I'll work out how the Java works.

Hi,

maybe a quick other solution, might be slower but no java needed :-)

 

Group Loop Start (by Value)

Counter Node

Loop End

 

Cheers, Iris

Ahhh... very nice solution, Iris! I should have thought of that. 

Hi!

I sorted this out like this:

image

I sorted my db by date and value and then ranked the counter by value. I do not know if it is faster but in my case it is fast enough.

My reply, though, is for something else…
How can I achieve something like this?

Value Sequentual Occurence Value
A 1
C 1
B 1
A 0
A 0
C 0
B 0
D 1
D 0

Hi @jorgemartcaam can you open a new topic for this? This one is really outdated, over 6 years old :slight_smile:

1 Like