Re-arranging data

[imrahu01]

Hi All,

I need some help. In this attached file you can see, there is are four columns named as SC Allocate, CA Allocate, WA Allocate and OR Allocate. I am just wondering is there any way I can move this data to SC, CA, WA, and OR without using a separate column for each of them.

[Thableaus]

Hi @imrahu01 

Would something like the attached work?

Replace the input file with yours.

Cheers.

[imrahu01]

Hi @Thableaus 

That looks great. But I have one more question if you kindly answer it for me that would be awesome. In excel I can do it in that format 

Cell AY1= Cell AY1(SC)  + Cell BJ1

Can I do something like that in alteryx so that it always give the sum of those two cells in one cell?

I want to do it for all four states.

[estherb47]

Hi @imrahu01 

Here's another approach, which is a total cheat/hack. After transposing all of the state and allocated columns, use a Select tool, changing the length of the Name column to 2. That cuts off the _Allocated piece of the name, just leaving the state.

A crosstab tool with Sum will then add up the appropriate data by state, combining the original number with the allocated number, no new columns needed.

Let me know if that works.

Cheers!

Esther

[estherb47]

@imrahu01 

With regards to your latest question, Alteryx isn't a spreadsheet, so we're not thinking in terms of cells, rather in terms of fields or columns.

When you change the name of the State_allocated field to just the two letter state, and then summarize by those states, that's equivalent to adding the two cells in Excel.

Does that make sense? We're looking for the commonalities, and then adding them together (both @Thableaus and I used the cross tab tool with a Sum function)

Cheers!

Esther

[Thableaus · Accepted solution]

You can use a Formula Tool and select the SC Column.

And you can do [SC] + [SC_Allocate]

And do for each one of them as well.

Cheers,