Is there a way to replace value in column A based on column B value?

[4d78bb8719d997eb9a26]

I would like to efficiently replace a null value in a column (StudyLoad) based on value in another column (Program).

Whether I use the IF() or replace functions, I can only work in a single column. Hence I have to work in multiple steps, merging the new column with the existing one on the second step.

Is there a function that allows me to validate condition and replace on the fly in a single formula / step?

So IF(columnA == "this", REPLACE(columnB, "value"))

Even better if I could use CASE() and clean up a bunch of possibilities in one swipe.

Thanks.

[Trifacta_Alumni · Accepted solution]

@Benjamin Sasin? Use edit column with formula transform and put the column you want to edit, suppose columnA.

Formula: IF(columnB =="this",REPLACE($col,"value"),$col)

You can also put a CASE() function in the formula box above.

Or you could also use a CASE on custom conditions transform to define cases and then Edit columns with formula transform with CASE transform output.

[4d78bb8719d997eb9a26]

Sorry Vardan, where exactly should I see the edit column with formula transform?

[Trifacta_Alumni]

From Search Transformations icon on the right corner you can search for edit column with formula transform. You can pretty much search for any transform or function incase if you don't find them under column suggestions, toolbar or column dropdown

[4d78bb8719d997eb9a26]

Okay thanks Vardan, I found it now.

Also may I point out that the REPLACE() function is not listed in suggestions? Neither could I find it in documentation.

[Trifacta_Alumni]

Aah you are right @Benjamin Sasin? there is no REPLACE() function, apologize for that. I guess i was lazily trying to replicate a REPLACE like characteristic.

So if you mention the columns in Edit with formula transform, they can be referred using $ symbol in Formula box. For example.,

Columns -> ColA

Formula - IF(ColB =="value","value",$col)

The above formula replaces ColA with "value" of ColB is equal to "value". If colB is not equal to "value" it retains same values that is referenced as $col which is equal to the columns/column mentioned in Columns box.