How can I cross tab data into predetermined columns

[matula23]

Hello Alteryx Community!

I have a problem that I hope some of the brilliant folks on here can help me with. This is my first time posting so please let me know if I should be looking somewhere else or if you need more information!

Problem:

I have a data set that looks something like the below. It includes a few columns. One for city, one for Step Number, and one for Usage Amount. 

My goal is to transpose this data in a way that I have 1 row for each jurisdiction and the Step Number and Usage Amount go into predefined columns. See below for an example of what I need my output to look like:

In this example, I know that I will not have any more than 10 Step Numbers/Step Amounts for any jurisdiction. I can have duplicate Step numbers (I.E. Chicago has two "K01" step numbers, but they have different step amounts associated with them, so these need to stay separate). Also, the step amounts and numbers do not always go up to 10 in which case I would like to show a Null or blank. I need the Step number and Step amount to be in adjacent columns and ascend in order. I've tried to use a text input to predefine my columns and I tried using cross tab to get my data in there, but I must admit I am fairly new to this and its giving me some trouble.

Any guidance or assistance would be very appreciated!

Thank you!

[apathetichell · Accepted solution]

Let's think about this - you need to get usage amounts and step nos in one column - so you know you'll need to transpose them. The issue you have is - how do you get the step# (kind of frustrating that you have two things called step number). I'd like to introduce you to the Tile tool. It's quite useful. I'd recommend something like TILE - UNIQUE VALUE CITY.

Then I'd recommend transpose with key columns city and tile_sequence number. You are transposing Step No and Usage Amount. Now you have to build your new column header category. I would do something in a formula tool like if [Name] contains "Step" then "Step"+tostring([Tile_Sequence_Num]) else "Step"+tostring([Tile_Sequence_Num])+"Amt" endif

Now I'm ready to cross tab. My key column is city. My header column is my new column - and my value is my value.

Try that - and see how far it gets you.

[matula23]

Apathetichell - Thank you! This is really cool. I've never used the Tile tool and combining that with transpose gets me to a good spot where I think I can figure out the cross tab portion from here. I appreciate the quick help!