Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

Re-arranging data

Highlighted
7 - Meteor

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.

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @imrahu01 

 

Would something like the attached work?

 

Replace the input file with yours.

 

Cheers.

Highlighted
7 - Meteor

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.

Highlighted
14 - Magnetar
14 - Magnetar

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.

EstherB47_0-1593031391822.png

 



Let me know if that works.

Cheers!

Esther

Highlighted
14 - Magnetar
14 - Magnetar

@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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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,

Labels