Using CASE statements to set conditional values

A CASE statement allows you to set column values when they fit the parameters you specify. The formula is:

CASE WHEN "Column Name" (condition)
 THEN 'value'
WHEN "Column Name" (condition2)
 THEN 'value2'
ELSE "Column Name"
END

This might be best explained with an example.

In my chart, I'm getting the sum of the Engagement Index, grouped by Activity Name. Here's what the Data Preview looks like:

I want to add a new column based on Engagement Index, where if Engagement Index is greater than 50,000, the new column should contain the string "High Engagement" for that row. If the sum is less than 50,000, I want the new column to contain the string "Low Engagement" for that row.

I'll start by adding an Add Column step in the Data Pipeline.

I'll call the column Engagement Group, and will put the following CASE statement in the Formula section:

CASE WHEN "Engagement Index" > 50000 THEN 'High Engagement' WHEN "Engagement Index" < 50000 THEN "Low Engagement" ELSE "Engagement Index" END

Here's what the Add Column step looks like, along with a data preview after the step is applied.

2014-09-03_15-38-52.png

Using a CASE statement to rename existing column values

If you're using a CASE statement to rename existing column values, you'll want to hide the original column and reorder your columns.

1. Add a Hide Columns step to hide the original column
2. Add a Reorder Columns step to reorder the new column so it is in the same place as the original.