How to combine certain column values (High, Medium) to display as an additional level (High, Medium , High & Medium)

Created
October 08, 2021 23:25

Date Posted: 2018-07-17
Product: TIBCO Spotfire®

Problem:

Solution:

Suppose we have a column in a table which stores four levels such as "High", "Medium", "Low" and "Very Low". Now in a cross table we can show these Levels and a metric such as "Dollar" value. There may be a business requirement to see additional levels such as "High & Medium", "Medium & Low", "Low & Very Low" in addition to the existing levels. The Cross Table will show all these levels and the "Dollar" value for each level. This can be achieved in Spotfire by using steps provided in the Resolution section below.. First create some calculated columns as shown below and then add a new data table from the existing data table and use transformation "Unpivot".

A). Insert > Calculated Columns like the following.

1). High = If([level]="High",[value])
2). Low = If([level]="Low",[value])
3). Medium = If([level]="Medium",[value])
4). Very Low = If([level]="Very Low",[value])
5). Very Low & Low = If(([level]="Very Low") or ([level]="Low"),[value])
6). High & Medium = If(([level]="High") or ([level]="Medium"),[value])
7). Medium & Low = If(([level]="Low") or ([level]="Medium"),[value])
ADD MORE COLUMN AS PER YOUR REQUIREMENT

B). Next add a data table using the existing data table and use transformation : Unpivot
        Transformation name: Unpivot
        Columns to pass through: (None)
        Columns to transform:
            High
            Low
            Medium
            Very Low
            Very Low & Low
            High and Low
            High & Medium

Also check the LevelAdditionDXP.dxp (see attached) for more information.