Map Only Certain Values from One Column to Another Column

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

  • .

  • GrantSmith
    GrantSmith Coach
    edited December 2021

    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!**
  • 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.


  • 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!**
  • 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

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