Multi row formula on multiple columns

[prpatel]

Hi,

I get a lot of data to analyze that comes from Excel where the user (or excel sheet developer) has merged rows together.  I can use the multi row formula tool with the statement "If row is blank, then use row-1 else use row end".  That's my pseudo-code...not Alteryx format.

In one use case I I have 7 of these such columns, and I've used 7 multi-row tools., and it looks like this...

Is there an easier way of doing this without the 7 multi-row tools.  I'm working on another dataset that has 15 of these, and am looking for a faster way out.

Thanks.

-prpatel.

[Luke_C]

Hi @prpatel 

You can transpose the data and apply the logic once, then cross-tab back:

[IraWatt]

Hey @prpatel, 

If you have to apply the same formula with the same tool to multiple columns you will want to transpose your data onto one column to apply the calculation. Like this:

This solution uses the transpose and cross tab tools which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how they work: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[patrick_digan]

@prpatel Transpose/Crosstab is certainly the usual way to go (as @IraWatt and @Luke_C have done), but I created the multi-row -field -column macro  to do the same without having to transpose/crosstab. It's a quirky little macro that I use every so often for cases like this!

[IraWatt]

@patrick_digan awesome macro should be made part of Alteryx Designer by default 👏

[NoelCarson]

An easier way could be to use formula tool create a Boolean column which is updated to '0' if blank and '1' if it has data. Then use a filter tool to remove the rows marked as '0'.

[Qiu]

I need to study this post since it attracted many big shots. 😁