CODEWITHSUNDEEP
calculated-columnsspotfire
NightLearner
NightLearner

Reputation: 305

identify when values in a column change in spotfire

I am trying to create a calculated column that flags/counts the changes in values across rows in another column, in Spotfire. Below is an example of the data types I'm looking at and the desired results.

My hope is that for each Location, and ordered along Time, I can identify when the values of "colors" changes and have running count so that each cluster of similar values between changes is given the same label (Cluster Desire 1) for each Location. It would be best if the running count of clusters can restart at each location but this is not crucial. Any help would be more than appreciated!

Example data and desired outcome

Upvotes: 0

Views: 345

Answers (1)

Gaia Paolini
Gaia Paolini

Reputation: 1492

I thought of a way to do it, relying on one intermediate column (I used two just to make it a bit clearer).

First: the concatenation of values for each row within its Location: called [concatString]

Concatenate(Concatenate([Color]) over (Intersect([Location],AllPrevious([Time]))),', ')

Spotfire defaults to comma followed by space as a separator: I could not find a way of changing that in this kind of expression.

Then within each [concatString] I remove repeated values. The complication is that the last one did not have the comma+space, and I did not manage to make the regular expression I am using understand that. So my workaround was to add a final comma+space to [concatString]. Hence the extra Concatenate(..).

The formula for the column without repetitions, [consolidatString] is:

RXReplace([concatString],"(\\w+\,\\s)\\1+","$1","g")

Then what we have achieved is an individual value for each line we want to group. We can then simply rank [consolidatString] to achieve the desired column:

DenseRank([consolidatString],[Location])

Upvotes: 1

Related Questions