Successive numbering of data groups using calculated columns

[Luke George 2]

I need to create a calculated column that looks at another data column containing groups of 0's and 1's and gives every group of 0 a successive integer. It's a bit hard to explain, but the example table below should make it clear.

 

 

 

Existing Column

Calculated Column

 

 

1

 

 

 

1

 

 

 

0

1

 

 

0

1

 

 

0

1

 

 

0

1

 

 

1

 

 

 

1

 

 

 

1

 

 

 

0

2

 

 

0

2

 

 

0

2

 

 

0

2

 

 

0

2

 

 

1

 

 

 

0

3

 

 

1

 

 

 

0

4

 

 

0

4

 

 

0

4

 

 

 

Is this kind of operation possible in spotfire if so, how

Thanks

[Gaia Paolini]

Hi. Try this, at the expense of two intermediate calculated columns:

rowID: RowId()

This is to establish a unique sequence of rows.

temp1:

If(([existingColumn]=0) and (([rowID]=1) or (([existingColumn] - Min([existingColumn]) over (previous([rowID])))!=[existingColumn])),1,NULL)

This will be 1 if: existingColumn is zero and: either it is the first row, or it changed from 1 to 0 from the previous row

calculatedColumn:

If([existingColumn]=0,Integer(Rank([existingColumn]) - 1 + Sum([temp1]) over (allprevious([rowID]))),NULL)

This adds to the rank the cumulative sum of temp1 so far.

Gaia

[Luke George 2]

Thanks Gaia, that works perfectly!

[Luke George 2]

Hi again Gaia,

 

I had another related question I thought you may be able to help me with. Suppose I wanted to create another culculated column that further subdivides the initial calculated column from the above example. This new calculated column would simply subdivide in groups of a set integer, say 3, as in the below example. Is there a way to do this

 

Initial Grouping	New Calculated Column
0
1	1
1	1
1	1
1	2
0
0
2	3
2	3
0
0
3	4
3	4
3	4
3	5
3	5
3	5
3	6
0
4	7

 

Thanks so much!

 

 

[Gaia Paolini]

I think that is probably would be best done in TERR. Can you post as a new question

 

Thanks. Gaia

[Luke George 2]

Sure thing. Here is the new question:

 

https://community.spotfire.com/questions/creating-group-subdivisions-using-calculated-columns

 

Thanks