Map Only Certain Values from One Column to Another Column

Options
micheleb
micheleb Member
edited December 2021 in Magic ETL

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

Answers

  • micheleb
    micheleb Member
    Options

    .

  • GrantSmith
    GrantSmith Coach
    edited December 2021
    Options

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • micheleb
    micheleb Member
    Options

    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.

    2021-12-09_10-32-04.png


  • GrantSmith
    GrantSmith Coach
    Options

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • micheleb
    micheleb Member
    Options

    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
    micheleb Member
    Options

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

Categories