6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com

Alteryx Designer Discussions

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

Collapsing similarly named/duplicate columns by summing values

alpro-23
5 - Atom

Hi all,

 

I am completely new to Alteryx and I am trying to do a workflow that can collapse all similarly named columns by summing the values within the column. My values currently are dummy variables hence all in 0 / 1. 

 

Thus far, I can't find any similar posts on Alteryx regarding similarly named columns and summing values. 


Would appreciate help from anyone on this topic!

 

** In my actual workflow, I have more than 100 columns that faces this issue... hence I chose not to do this manually (ie. typing 'pool' and 'roof') in my workflow. Any way to automate this?**

 

 

Input:

alpro-23_0-1619438620431.png

 

Output:

alpro-23_1-1619435109482.png

 

 

4 REPLIES 4
JosephSerpis
15 - Aurora
15 - Aurora

Hi @alpro-23 I mocked up an approach to tackle this challenge. The approach essentially pivots your data and then uses the text to column tool to split your fields based on _ and then aggregates the data and pivots back to a horizontal format. Let me know what you think?  

 

Pivot_260421.JPG

alpro-23
5 - Atom

Thanks Joseph for your quick reply! 

I'm afraid I may have misled the question. My actual columns are not separated by a delimiter ('_'). Instead, they just have a number at the back to differentiate the column names. 

 

In this case, I'm not sure if a number can be used as a delimiter? 

I'm wondering if a combination of regex and wildcard character would work... But I'm quite new to coding and alteryx so I'm not sure how I can actually do it...

JosephSerpis
15 - Aurora
15 - Aurora

I've amended my workflow it uses regex to find the digit at the end of the column name.

 

Pivot_260421.JPG

RaviP
8 - Asteroid

Hi @alpro-23,

 

If the column names are having numbers as suffix then you could also this

 

RaviP_0-1619440424843.png

 

Labels