Consolidating similar row values based on column reference

[bryan tinker]

Hi Folks.

Looking for some direction on data manipulation within Spotfire. A generic version of what I am attempting to do:

 

I have good reference data in column 1, but column 2 is manually entered, and so there is a mix of data - it all means the same, but its slightly different, so Spotfire organizes it as unique. Is there a way to manipulate the data in column by ranking or choosing the first value associated with column 1 I dont really care which one it is as long as they are all the same.

 

The end goal is to be able to create a cross table based on column 1, where 'data to fix' is aggregated properly.

 

 

 

 

 

 

 

Good data

data to fix

Corrected data

 

 

part 1

Widget

Widget

 

 

part 1

widget

Widget

 

 

part 1

wigdte

Widget

 

 

part 2

long wheel

long wheel

 

 

part 2

lng whl

long wheel

 

 

part 2

long whl

long wheel

 

 

part 3

8 ft post

8 ft post

 

 

part 3

8 foot post

8 ft post

 

 

part 3

8' post

8 ft post

[Khushboo Rabadia]

You can create a calculated column i.e. correct data using over function

Frist([Data to fix]) over([Good Data])So for each value of good data, it will have the first value of data to fix and use the same for all rows with same good data value.