Sum data based on certain fields, then put the sums into rows in a new column in excel

I have a very large data set that Alteyrx is pulling directly from a data source and I essentially want to do a a pivot on the data but in alteryx, then create a new file with the pivoted data.

I want to sum the data based on an attribute from column one, and then put the sums of each attribute into a row in a new column for the new file, so the new file is a summary of the data.

You can see in the picture, column one has identifiers such as 245, 203, 209, 220, etc. I want alteryx to sum up the amounts in column 2 based on the Column 1 identifier first, then put the sums and the corresponding identifier into two columns in a new file.

Alteryx Question.PNG

Workflow

Datasets

Custom Tools

Best Practices

Dynamic Processing

Preparation

In Database

Interface Tools

In-Database

Accepted answers

rzdodson

@jportello do you have a copy of what you expect the final output to look like? I am a bit confused what you need the final output to look like.

Edit: it sounds like you'll need to insert a formula tool to do some data cleansing to treat the numbers in parenthesis as negative values (advise if different) with a formula like this:

Trim(Replace(ReplaceFirst([Values], "(", "-"),")",""))

The, use a Cross-Tab tool on the Identifier to return the aggregated values. Finally, create a dynamic output path to generate your outputs.

rzdodson

@jportello would have to take a look at the key columns you selected within your Cross Tab tools. I believe if they are truly a key value, you can sort on that key field, then use a Join tool to bring them together. When you join, you'll can join on Record Position at that point. This is assuming that the outputs have the same values in both tables. I would need to see what the expected output is to confirm whether this approach would help solve for you.

The Union tool is really intended for "stacking" your data vertically; whereas, the Join and Join Multiple tools here will "merge" these tables so they appear horizontally. From the sound of your previous post, I would recommend the Join/Join Multiple approach.

All comments

rzdodson

@jportello do you have a copy of what you expect the final output to look like? I am a bit confused what you need the final output to look like.

Edit: it sounds like you'll need to insert a formula tool to do some data cleansing to treat the numbers in parenthesis as negative values (advise if different) with a formula like this:

Trim(Replace(ReplaceFirst([Values], "(", "-"),")",""))

The, use a Cross-Tab tool on the Identifier to return the aggregated values. Finally, create a dynamic output path to generate your outputs.

jportello

Thank you for your response zdodson! I have attached an example of what I want it to look like but I'm going to give your answer a shot and get back to you. I also wanted to ask you an additional question. You can see the ideal output in the attachment as well.

EDIT #2: I just used your suggestion and it did exactly what I wanted!! Thank you SO MUCH!

Edit: Also, the numbers in parenthesis look like that because I wanted to show the example clearly in Excel, but it actually is in this format: -55134, so the numbers are good to go for manipulation.

Alteryx Question Example.PNG

rzdodson

jportello

So you can see my in my screenshots "Stream 1" and "Stream 2" that the Cross tab tool worked to sum the activity together based on Identifier 1(GMR001) based on two other different identifiers as I mentioned in my question.

Youll see that they have the same GMR001 lists and so I just want to put the selected columns from stream 2 after the columns I selected from stream 1. Does that make sense? Let me know if I can provide additional details.

Stream 2.PNG

Stream 2 Selected Columns.PNG

Stream 1.PNG

Stream 1 Selected Columns.PNG

jportello

Oh my gosh I just realized why I couldnt figure out how to match them... I wasnt including the first column when I selected the columns that I needed so when I was looking at the Join tool there was nothing for me to connect it on.

After including the GMR001 column then I joined it on that! Thank you so much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703