Hi Team,
I have below table. I want '0' to be replaced with the above value whichever it is. For example Column 'A' has -169, I want to replace to the below column where we have '0' and for -1697 to be replaced to below column where we have '0'. and also divided by two for the new column which is a1 c1.
a | c | a1 | c1 |
-169 | -188.2 | ||
0 | 0 | ||
-1697 | -188.1 | ||
0 | 0 | ||
-58 | -64.5 | ||
0 | 0 | ||
-219 | 0 | ||
0 | 0 | ||
0 | 14.1 | ||
0 | 0 | ||
65 | 106.7 | ||
0 | 0 | ||
0 | 14.1 | ||
0 | 0 | ||
-581 | -64.5 | ||
0 | 0 | ||
-21 | 0 | ||
0 | 0 | ||
-581 | -64.5 | ||
0 | 0 | ||
-21 | 0 | ||
0 | 0 |
Solved! Go to Solution.
You want to use the multi row formula tool to fill down and then convert it to double and the perform operations..
Please find attached workflow.
Here you go..
@mmustkee
I tried to be more dynamic, so it will work for not only for 2 columns, also 3 or more.
Hi @mmustkee — Try this macro by @danielkresina
You need to configure it for your use case:
1st keep the "Fill Down Over Nulls.yxmc" in the same folder, then configure the macro like below:
(FYI, you will need to provide the actual layout of your fields in the "Input Macro" tool >>> run the macro for testing >>> Save it)
Now, open "763295_Coulmn data to be replaced__vA.yxmd" >>> Right click and insert the macro >>> select your fields >>> Run it.
That's it!
Hope this helps!
Hi Qui,
Thanks for the solution and it worked too.
But i just want to confirm, Do we not have any formula which we can simply apply without using sort, multi formula or record ID. for Example if we have "0' so please use value from upper column till we have a value and same to next "0" if found below:
Kindly advise.
Hi @mmustkee
Formula tool works only on row-level (i,e it only looks to its specific row for calculation). If we want to use calculation which is dependent on upper/lower row then we need multi-row formula tool. And since you want to use this formula for multiple columns and multi-row formula tool can output only one column at a time. So @Qiu is using record id, tranpose, sort method to apply multi-row formula for multiple column in at a single tool.
So these steps are needed and this would be the way to go to solve this usecase.
Hope this helps 🙂
Hi @mmustkee , as @atcodedog05 and @Qiu have pointed out, there is a need to make this dynamic and to relate to other rows in the dataset.
I've attached a tool I wrote to autofill rows with the value above until a new value is found. This will address one question you have.
M.
@mceleavey @atcodedog05
Thank you so much for the detailed explaination. I can not do any better. 😁