Dynamically Changing column Header Based on ANother Column Value

[tjanwe]

Hi!

Might be a stupid question but I would like to change the column header "MetricValue" to the data in "%WeeksBreached" column. The data in "%WeeksBreached" column will all be the same throughout the columns, as it is appended. However, the number can be different eg 0.2 or 0.3 or 0.4. Please see sample input and expected output below:

Input

KRI	MetricValue	%WeeksBreached
abc	3	0.2
def	6	0.2
ghi	2	0.2

Please see below output, where the 20% comes from the 0.2 in "%WeeksBreached" column.

Output

KRI	20%WeeksBreachValue	%WeeksBreached
abc	3	0.2
def	6	0.2
ghi	2	0.2

[abacon · Accepted solution]

@tjanwe I would do the following -

• Start by adding a recordID tool
• Then using a transpose tool selecting the RecordID field as the key column and the rest of the columns as the data columns.
• Next you will want a formula tool and create a field called "New Headers" with a formula like this - if contains([Name],"%WeeksBreached") then tostring([Value]*100) + [Name] else "" endif.
• Now you will use a multi-row formula tool that updates the [Name] with this formula - if contains([Name],"MetricValue") then [Row+1:New Headers"] else [Name] endif.
• Last piece will be a Cross Tab tool grouped on the Record ID with the [Name] field as the column headings and the [Value] field as the values.

This should get you what you need.

If this works for you, please mark it as the solution so others may find it quicker.

[tjanwe]

Thank you!