Alteryx Designer Desktop Discussions

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

Combining Similar Columns and Group by/Sum by multiple columns

aaggh2003
5 - Atom

Hi, I am getting some input data as below:

 

 Name 1Name 2Group 1Group 2ValueA 1ValueA 2ValueB 1ValueB 2
XXX 1 100 200 
 YYY 2 100 200
 XXX 1 300 300

 

Note that if Name doesn't have a value than the respective columns afterwards will be null. (i.e. Name 1 is null then null in Group/ValueA/ValueB 1)

 

and I would like to combine columns based on the numbering while summing the values:

NameGroupValueAValueB
XXX1400500
YYY2100200

 

I've tried using Transpose and Cross Tab but run into trouble as the Value column will have both string and number formatted columns. So how can I group by combining several string columns and sum multiple columns?

 

Thanks in advanced for any help and please let me know if the question is unclear.

4 REPLIES 4
Qiu
21 - Polaris
21 - Polaris

@aaggh2003 
Just as you mentioned, there are both string and number formatted columns, so we have to seperate them and I use a Regex filter.
And I use record ID the keep tracking the records before I can join them togher.

In your real data, it can be much complex for removing the postfix in column names.
Hope it helps.

0601-aaggh2003.PNG

alisonpitt
11 - Bolide

You can do this without regex, too, since the column names are nicely formatted. It is important to use a RecordID though, I agree with Qiu :)

alisonpitt_0-1685574839497.png

alisonpitt_1-1685574911340.png

 

 

flying008
15 - Aurora

Hi, @aaggh2003 

 

Another way for you , maybe this is a dynamic solution:

 

录制_2023_06_01_08_03_51_762.gif

 

******

If can help you get your want, please mark it as a solution and give a like for more share. 

Otherwise please upload sample data for get advanced way.

aaggh2003
5 - Atom

I added a new field in the formula by assigning an unique group_id for each _X, and uses both record_id and group_id during the cross tab tool, all good now!

 

Thanks everyone!

-------------------

I realized I misled you all by showing an input that did not have data in _1 and _2 at the same time, which is actually possible and with the cross tab tool having "First" value makes those _2 values disappear.

 

See attached for the sample record, which got values in all columns, and the output I am looking for should be:

NameGroupSum_ValueASum_ValueB
250Other0.015772.57
104Other12500237.45

 

I tried creating multiple transpose tools while adding numbers to create new record id and use Union tool at the end but I actually have 10 columns for each header, not sure if this is the best way to do it.

 

Apology for making the confusion. 

Labels
Top Solution Authors