Map Only Certain Values from One Column to Another Column

micheleb

Hi,

I have 2 columns. Column A has one set of values - I only want the value of Column B changed if the Column A value = Complete.

Example

Progress Column A ------Progress Column B ------Result Progress B

Completed --------Started ------ Completed

Started ------ Completed ----- Completed

Started ------ Started ------ Started

IF Column A = Completed AND Column B = Started THEN change the value of Column B to Completed Else Do Nothing

I can't make a case statement work cause it requires and else statement. Any help is appreciated

micheleb

.

GrantSmith

Hi @micheleb

You can use a case statement to conditionally set column B to completed if column A is completed

CASE WHEN `Progress Column A` = 'Completed' THEN `Progress Column A` ELSE `Progress Column B` END

If you don't want an extra column in your dataset from this beast mode you can use the same formula in a Formula tile in the new Magic ETL and set the column name to be the same as the existing one and it'll overwrite the value for you.

micheleb

Hi,

Thanks - I'm not sure how that would work I just get an error.

I only need the value of Content Progress changed if the Journey-Pathway Content Progress = Completed;

I don't want to create a second column.

GrantSmith

You're missing a single quote and a few back ticks (these designate column names).

CASE
WHEN `Journey-Pathway Content Progress` = 'Completed' THEN 'Completed'
ELSE `Content Progress`
END

Doing this as a beast mode will create a second column in your dataset as that's how beast modes function. You can rename your column's Display Name value clicking on the column name and changing Label value. Then you can use this new column / beast mode field in your report instead of the old column so it's only displayed once.

micheleb

Still, get an error - I'm using the Add Formula in Magic ETL - so I'm guessing this process just doesn't work in Magic ETL.

Thanks for your help

micheleb

I figured out what I was doing wrong - thanks for your help!