Multi row formula on multiple columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@patrick_digan awesome macro should be made part of Alteryx Designer by default 👏
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I need to study this post since it attracted many big shots. 😁
![](/skins/images/1A7F54316481E10DBCA4A87A32E06CC6/responsive_peak/images/icon_anonymous_message.png)