X-Y Axis Data Visualization - Transpose only a few columns

[mvyjayanti]

Hi,

I'm trying to transpose only a few of my columns so I can visualize my data in a heat-plot.

Eg. of my data (the following data is fake and made-up just for this post):

Year	Airline	Meal	Dietary Restriction	Count
2000	United	Breakfast	Vegetarian	150
2000	American	Lunch	Non-vegetarian	175
2000	Delta	Dinner	Non-vegetarian	105
2001	United	Dinner	Vegetarian	7
2001	Air India	Breakfast	Vegetarian	145
2002	American	Dinner	Non-vegetarian	139

Desired Results:

United

United

United

United

United

United

American

American

American

American

American

American

Breakfast

Breakfast

Lunch

Lunch

Dinner

Dinner

Breakfast

Breakfast

Lunch

Lunch

Dinner

Dinner

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Year

2000

150

175

2001

7

2002

139

I hope this makes sense. 

Basically, I want to transpose everything but my year column so I can filter by year and look across to see all the available data for each airline, meal, and dietary restriction.

I'd highlight cells by value at that point.

[Jotigautam]

@mvyjayanti 

Could you please explain why three line header is needed. If the requirement is to just filter by year and show heat map, you can create a string by clubbing three columns and then cross tab

[mvyjayanti]

@Jotigautam

I need the multi-line header as I want the headers to show all possible values. The table will show blanks where those values do not exist (e.g. American - Lunch - Vegetarian)

A future step would be to merge the headers for visualization purposes.

I was able to arrange the table in this way via excel pivot table: 

- Rows: Year

- Columns: Airline, Meal, Dietary Restriction

However, I would prefer to do this in Alteryx.

[Amol_Telore · Accepted solution]

Hey @mvyjayanti 

Here is my solution. Hope this resolves your issue.

Note :- Thou, we are showing first 3 records as Header in report and it is dynamically working. It is still part of data and if you try to read output file again in another process, it will be read as data (from 1st row) not header.

[mvyjayanti]

Thanks @Amol_Telore

Do you know if there's a way to see all possible values in the headers?

e.g. 

United	United	United	United	United	United
Breakfast	Breakfast	Lunch	Lunch	Dinner	Dinner
Vegetarian	Non-vegetarian	Vegetarian	Non-vegetarian	Vegetarian	Non-vegetarian

[Amol_Telore]

@mvyjayanti Your input data delta and Air India airlines which is not present in desired output. Are we excluding any other airlines except United and American?

[Amol_Telore]

As per desired output, I have done some changes in the workflow. PFA.