Creating relationship between separate columns in Spotfire

[Andrea Ackroyd]

I require assistance in how to collate data from several columns.

For example, I have two workers, A & B, each working at a different station, then a measurement of the productivity of that station.

If I want to find the cumulative sum of the productivity of each worker, from both stations, with the data in this format, how would I do it

To make it more complicated I would like the result to be a channel for Station 1, showing the cumulative sum of productivity for worker A (completed at any station), when the current worker at station 1 is worker A, and vice versa. An equivalent channel will be used for Station 2, in this example. Please see desired output in example below.

I will not know the number of different workers, or their possible names, in advance, so any intemediary channels could not be based on assumptions about that. There are, however, only ever 2 stations.

Many thanks in anticipation

[Khushboo Rabadia]

You can achieve by using transformations. I have attached dxp showing the same. First unpivot your data such that both stations are in one column.

Then create row id column which will be useful to get all previous records. Once stations are in one column, then you can just sum up prod values from both station 1 and station 2 over all previous rows as well.

Then again transform the data into original format using pivot function.

2. Select Data > Transform data...

Added transformations

Transformation name: Unpivot

Columns to pass through:

Prod1

Prod2

Columns to transform:

Station1

Station2

Category column name: Category

Category column data type: String

Value column name: Value

Value column data type: String

Empty values included: Yes

 

Transformation name: Calculate new column

Column name: id

Expression: RowId()

 

Transformation name: Calculate new column

Column name: newprod

Expression: Sum(case when [Category]="Station1" then [Prod1] else [Prod2] end) OVER (Intersect(AllPrevious([id]),[Value]))

 

Transformation name: Calculate new column

Column name: finalid

Expression: First(RowId()) OVER ([Prod1],[Prod2])

 

Transformation name: Pivot

Row identifiers:

finalid

Prod1

Prod2

Value columns and aggregation methods:

none(Value)

none(newprod)

Column titles: Category

Column naming pattern: (%V) for %C

Transfer columns and aggregation methods: (None)

Transfer column naming pattern: %A(%T)