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

[jportello]

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. 

[rzdodson · Accepted solution]

@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. 

[jportello]

I have an add on question to this. So we completed that step of putting sum of activity based on identifier 1 and put them into different columns (based on identifier 2). I did that same thing where I summed the activity based on identifier 1 and put them into different columns (based on a DIFFERENT identifier, idenifier 3) and then I used the "select" tool to keep the columns that I want from each, now I want to put them all together in an excel.... the union tool isnt exactly what I want.  

[rzdodson · Accepted solution]

@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.

[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.

[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!