Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Coulmn data to be replaced

mmustkee
8 - Asteroid

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.

 

aca1c1
-169-188.2  
00  
-1697-188.1  
00  
-58-64.5  
00  
-2190  
00  
014.1  
00  
65106.7  
00  
014.1  
00  
-581-64.5  
00  
-210  
00  
-581-64.5  
00  
-210  
00  

 

7 REPLIES 7
HW1
9 - Comet

@mmustkee 


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..

Qiu
19 - Altair
19 - Altair

@mmustkee 
I tried to be more dynamic, so it will work for not only for 2 columns, also 3 or more.

0524-mmustkee.PNG

vizAlter
12 - Quasar

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:

vizAlter_0-1621839210691.png

(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.

vizAlter_1-1621839521148.png

 

vizAlter_2-1621839729878.png

 

That's it!

 

 

Hope this helps! 

mmustkee
8 - Asteroid

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.

 

atcodedog05
22 - Nova
22 - Nova

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 🙂

mceleavey
17 - Castor
17 - Castor

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.



Bulien

Qiu
19 - Altair
19 - Altair

@mceleavey  @atcodedog05 
Thank you so much for the detailed explaination. I can not do any better. 😁

Labels