Calculate column to eliminate duplicate values

[christopher sawtelle 2]

Heres what I am trying to do: I have a Date column and a column with production values, one value per month. In this column the dates (i.e. Jan-Dec for 2012) are repeated several times and therefore so are the production values. I am attempting to create a calculated column that will give me these production values but only one time for each month. In other words only one occurrence of Jan, Feb, Mar etc.

 

Thanks for any thoughts.

[Manoj Chaurasia]

This is a good solution. I would also like to figure out a way to get this result in an existing table by adding a calculated colmn.

[Manoj Chaurasia]

Add a calculated column "Rank" with formula rank(rowid(),[Date],[Volume]). This will place a value of '1' next to the first unique combination of Date and Volume. You can then use a filter in your visulization remove the non-'1' (repeats) or add another calculated column "NewVolume" with formula if([Rank]=1,[Volume]). All repeat rows will be empty and will not show up. Then use [Date] and [NewVolumne] in your visualization.

 

Rank function

http://stn.spotfire.com/spotfire_client_help/ncfe/ncfe_ranking_functions...

[Manoj Chaurasia]

Good idea! I'll give it a try on Monday when I get back to work.Thanks!

[Mark Mneimneh]

Hi

 

Is this what you have in mind

 

http://spotfirecommunity.spotfire.com/community/blogs/tips/archive/2009/12/...

 

Thanks

[Jose Leviaguirre]

Sometimes I add a pivot and just transfer the rest of the rows

Select Insert > Transformations...

Added transformations

Transformation name: Pivot

Row identifiers: Unique ID

Value columns and aggregation methods: (None)

Column titles: (None)

Column naming pattern: %C

Transfer columns and aggregation methods: First(Production Date) First(Region) First(Operator)

Transfer column naming pattern: %T