Is there a way to set multiple columns to a specific value based on a condition ?
I can only think of unpivoting the dataset to do the replace.
Are there other ways because in wide datasets, unpivoting creates datasets with a lot of columns.
Maybe such functionality can be added in the table-wide replace action? Replace in the following columns if it satisfies the rule entered in that action.
For large wide datasets you can use “Iterate columns” and apply the condition in the iterated module.
I have created a sample dataset with my EasyMorph solution. Could you have a look at it and see if there’s a better more general way in which we can do such a replace ? I could not immediately see how to do this more easily.
The goal of this project is to set ALL B-columns and columns V01 and V02 to 0 IF V03 = 0 and sum(V04:V08) = 5000.
I use unpivot actions with a rule to achieve this.
demo_replace.zip (16.5 KB)
The solution looks good to me. There can be various ways how to identify columns that should be set to 0. In your solution you used a regular expression which is fine. As an alternative, the names of the columns to set to 0 could be marked using an external reference table - this can be better in cases when a regular expression would’ve been too complex. Or marked using EasyMorph text functions if, again, a regular expression would’ve been too complex. In this case, the regular expression is simple so it’s fine.
PS. By the way, I liked how clean and easy to read your solution is. Very idiomatic EasyMorph style. Excellent job!
Thanks for checking my solution !