Is it possible to use dynamic formulas to operate multiple columns?

[Ivanmt01]

Hi Community, 

I would like your support with this question.

Is it possible, I don't know if with the formula tool, separate these columns into positive and negative values all the columns at once?

I have tried and I know that with formula I can do one at a time, but is it possible to do it dynamically with a single expression or using a tool?

[benakesh]

Hi @Ivanmt01 ,

Need more details about expected output  or you can share expected output . 

You can transpose , filter  and  cross tab  the data  together .  The filter separates +ve and -ve numbers. Sample wf attached for reference.  

[TomWelgemoed]

Hey @Ivanmt01 ,

Yes, and I think the attached is a nice and clean solution.

You pivot the values first so you can get all the values in 1 column. Once that is done, all you need to do is give each value a column name (either positive or negative) and then you can cross-tab it back into the appropriate column.

Regards,

Tom

[Ivanmt01]

Hi, @TomWelgemoed 

i have problems to open your solution in alteryx 😞 

[Ivanmt01]

hi @benakesh 

i would like something like that

[TomWelgemoed]

Mmm, maybe different Alteryx versions...are you on 2019.3? I just upgraded, so that could be it?

Here is an image - there are only a few tools involved so maybe that helps (together with my instructions in the earlier message.

Regards,

Tom

This is an image of the Cross-tab.

[TonyA · Accepted solution]

Here's one solution. This uses a crosstab tool and there are a couple of things you need to keep in mind. The crosstab tool generally sorts columns it processes left to right, lowest to highest. Also, it replaces any special characters or spaces with underscores. In this solution, I renamed the columns to get the plus and minus signs, but did not do anything about the sort order. If that is important you can add a prefix to each column name before crosstabbing and then remove it afterwards with Dynamic Rename (similar to what I did to replace the plus and minus signs).

Please mark this as a solution if it resolves your question.