How to have Data Appended in a Row After Cross-Tab

In my current Alteryx workflow, I used the cross tab function to have by data broken out by columns rather that rows. For instance, say the data was:

	January	Feb	March
Candy	1	2	1
Sports Drinks	3	4	5
Snacks	4	3	2

Instead, I wanted to see the data as such:

	Candy	Sports Drinks	Snacks
January	1	3	4
Feb	2	4	3
March	1	5	2

I have been able to do that successfully through the cross-tab tool, but here is the issue I am facing. Another report I want to compare it against, orders their inventory differently, and so the second report may show the headers as: "Snacks, Sports Drinks, Candy".

The order of the second report is consistent and I have a sheet with the Column Number that each inventory item should belong in. So what I need help doing is:

1) Figuring out how I can insert a blank row into my data after the Cross-tab

2) Essentially VLOOKUP what column number each inventory item belongs in (this column number would go into the blank cell I created in the row under neath the header)

3) Horizontally sort the data so that the order is ascending

Does anyone have any ideas? Thank you all in advance.

Transformation

Preparation

Accepted answers

estherb47

Hi @rishipatel3

No need to add the extra row to set the order, you can do this within a few tools.

You have the order of the fields in the second table, so we just need a way to assign those to the item. By transposing your first table, then changing the field size to a bit larger with a Select tool, you can join that number in. A formula tool prepends the number to the field name, and then a crosstab tool rebuilds your table, with everything in the correct order.

Finally, a dynamic rename tool, using a formula to rename all of the fields:

regex_replace([_CurrentField_],"\d+(.*)","$1")

Let me know if that helps.

Cheers!

Esther

CustomOrder.yxmd

All comments

john_miller9

@rishipatel3

If I understand correctly, you are looking to compare inventory of two separate reports that have both a month and category dimension. If that's correct, I'd suggest doing the comparison with transformed, tall data, rather then comparing cross-tabs. What exactly are you trying to determine (e.g. how much more/less inventory #1 is compared to inventory #2 for a given month/category)?

JosephSerpis

Hi @rishipatel3 I mocked up something I think you are asking for.

Union.yxmd

rishipatel3

So I need to utilize the cross-tab because I need the data broken out over multiple columns rather than multiple rows for each month. The system used to present the data this way, and so all of the rest of the processes are based on this format.

rishipatel3

Hey! Thanks for building out the mock-up. Unfortunately, I was envisioning something a little different. So more so imagine in that blank row you inserted, I was to pull in the column number that I want each column to be, so that if I sorted the data based on that row, it would reorder. I know the wording is a little unclear, it's just not as easy to verbally state. Let's try this:

This is my data as of right now:

	Candy	Powerade	Gushers	Twix	Gatorade
January	1	3	4	2	3
Feb	2	4	3	2	3
March	1	5	2	1	3

I have a second sheet that looks like this:

Candy	4
Powerade	2
Gatorade	1
Twix	5
Gushers	3

I want to first add a row so my data looks like this:

	Candy	Powerade	Gushers	Twix	Gatorade

January	1	3	4	2	3
Feb	2	4	3	2	3
March	1	5	2	1	3

Then I want to pull in the column numbers into that blank row:

	Candy	Powerade	Gushers	Twix	Gatorade
	4	2	3	5	1
January	1	3	4	2	3
Feb	2	4	3	2	3
March	1	5	2	1	3

Then I need to sort that row with the column numbers in ascending order:

	Gatorade	Powerade	Gushers	Candy	Twix
	1	2	3	4	5
January	3	3	4	1	2
Feb	3	4	3	2	2
March	3	5	2	1	1

Does that help visually show what I'm trying to do? The data I am working with is hundreds of columns, so I have to try and streamline the process the best I can.

estherb47

Hi @rishipatel3

No need to add the extra row to set the order, you can do this within a few tools.

Finally, a dynamic rename tool, using a formula to rename all of the fields:

regex_replace([_CurrentField_],"\d+(.*)","$1")

Let me know if that helps.

Cheers!

Esther

CustomOrder.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703