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): YearAirlineMealDietary RestrictionCount2000UnitedBreakfastVegetarian1502000AmericanLunchNon-vegetarian1752000DeltaDinnerNon-vegetarian1052001UnitedDinnerVegetarian72001Air IndiaBreakfastVegetarian1452002AmericanDinnerNon-vegetarian139 Desired Results: UnitedUnitedUnitedUnitedUnitedUnitedAmericanAmericanAmericanAmericanAmericanAmerican Breakfast BreakfastLunchLunchDinnerDinnerBreakfast BreakfastLunchLunchDinnerDinner VegetarianNon-vegetarianVegetarianNon-vegetarianVegetarianNon-vegetarianVegetarianNon-vegetarianVegetarianNon-vegetarianVegetarianNon-vegetarianYear 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.

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

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

American

Breakfast

Lunch

Dinner

Breakfast

Lunch

Dinner

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Vegetarian

Non-vegetarian

Year

2000

150

175

2001

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.

Common Use Cases

Transformation

Accepted answers

Amol_Telore

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.

Mvyjayanthi_Reporting.yxmd

All comments

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

Hey @mvyjayanti

Here is my solution. Hope this resolves your issue.

Mvyjayanthi_Reporting.yxmd

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.

Mvyjayanthi_Reporting.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335