Search code examples
calculated-columnsspotfirepartitiontibco

Spotfire - Partition by column and number rows in partition consecutively

I imported a simple list of IDs into Spotfire. I would like to create a calculated column with consecutive numbers for identical IDs. The numbering should start at 1 for each ID.

Here is an example:
ID
A
A
A
B
B
C

The result should look like this:
ID NUMBER
A 1
A 2
A 3
B 1
B 2
C 1

I searched for similar questions in Stackoverflow and I tried the previous function in combination with a hierarchy. I also had a look at the help files. So far I was not successful.

Any help is highly appreciated. Please let me know if I missed a similar question or if my question needs clarification (this is my first question at StackOverflow).

Thank you very much in advance,

Adrian

Solution

  • I was able to do this by adding RowId() as a calculated column called [row], and then calculating [NUMBER] as Count([ID]) over (Intersect(AllPrevious([row]),[ID])).