How to make a TERR expression function that can flag if the values is a row change.

[Fabien Laugier]

Hi, I am wondering if it is possible to write a TERR expression function to create a new column that has a different value for each cluster of similar values in another column. I have an example table below where each cluster or group of similar values in the "Color" column is given a distinct number that increases every time the row value changes. I would like this to always be done in the order of the "Point" column. ANY help would be more than appreciated!

 

 

 

Point

Color

DesiredFlag

 

 

0

Red

1

 

 

1

Red

1

 

 

2

Red

1

 

 

3

Blue

2

 

 

4

Blue

2

 

 

5

Red

3

 

 

6

Blue

4

 

 

7

Blue

5

 

 

8

Red

5

 

 

9

Red

5

 

 

10

Red

5

[Fabian Duerr]

I think the easiest way to solve this is using a data function.

Define your table as input a new column will be the output.

 

Make sure that you sort table within the data function if your original table is not already sorted.

Initialize your new column with 1.

Then start with a "for loop" in the second row and check if the value in the COLOR column is the same as in the previous row. If yes, set the value of the new column equal to the value of the previous row. If no, increase the value of the previous row by 1. Do this until you reach the end of the table

 

Maybe you will find the tutorial in this post useful:

https://community.spotfire.com/questions/self-referential-calculated-column

Let me know when you get stuck and I will further support.

[Gaia Paolini]

Maybe you can also do it with a calculated column (plus an intermediate one).

First define the intermediate column to hold the previous color:

PrevColor: SN(ValueForMax([Point],) over (previous([Point])),)

Then define a column that first marks a change of Color as 1, then adds them up:

Sum(If([PrevColor]=,0,1)) over (AllPrevious([Point])) + 1

I am presuming your Blue at Point 7 is a 4 (typo)