Hi everyone, I am trying to change the following data into a pivot table. I am totally lost on how to do this. Any ideas? I have attached the book1 file where there is a raw data format and the desired output file. If someone can help, I will be highly appreciative. Thanks, Sri Raw data: Income statementJan Feb Mar ARSAUDBGLARSAUDBGLARSAUDBGLA010T-Total Vehicle Sales -- Dealers - - - - - - - - - A011T-Total DH&D - - - - - - - - - A012-Dealer Margin Holdbacks - - - - - - - - - A015-Other Revenue Reductions - - - - - - - - - - - - - - - The desired format is like a pivot table TimeAccount lineCurrencyJan 2017A010T-Total Vehicle Sales -- DealersARSJan 2017A011T-Total DH&DARSJan 2017A012-Dealer Margin HoldbacksAUDJan 2017A015-Other Revenue ReductionsAUDJan 2017A020-Vehicle Sales -- OutsideAUD---Feb 2016A010T-Total Vehicle Sales -- DealersARSFeb 2017A011T-Total DH&DARSFeb 2018A012-Dealer Margin HoldbacksARS Book1.csv

Raw data to Pivot table

Hi everyone, I am trying to change the following data into a pivot table. I am totally lost on how to do this. Any ideas? I have attached the book1 file where there is a raw data format and the desired output file. If someone can help, I will be highly appreciative. Thanks, Sri

Raw data:

Income statement	Jan			Feb			Mar
		ARS	AUD	BGL	ARS	AUD	BGL	ARS	AUD	BGL
A010T-Total Vehicle Sales -- Dealers	-	-	-	-	-	-	-	-	-
A011T-Total DH&D	-	-	-	-	-	-	-	-	-
A012-Dealer Margin Holdbacks	-	-	-	-	-	-	-	-	-
A015-Other Revenue Reductions	-	-	-	-	-	-	-	-	-
					-	-	-	-	-	-

The desired format is like a pivot table

Time	Account line	Currency
Jan 2017	A010T-Total Vehicle Sales -- Dealers	ARS
Jan 2017	A011T-Total DH&D	ARS
Jan 2017	A012-Dealer Margin Holdbacks	AUD
Jan 2017	A015-Other Revenue Reductions	AUD
Jan 2017	A020-Vehicle Sales -- Outside	AUD
-	-	-
Feb 2016	A010T-Total Vehicle Sales -- Dealers	ARS
Feb 2017	A011T-Total DH&D	ARS
Feb 2018	A012-Dealer Margin Holdbacks	ARS

Book1.csv

Preparation

Accepted answers

pcatterson

This is a bit arduous, but I think it gives you the basics on how to transpose data. There is nothing in the source data to tell me how the year is determined, so I left that undone:

PivotParse.yxmd

All comments

MSalvage

@ssubra60,

I hope I did this in the right direction. I was a little confused whether the Excel sheet was indeed the raw data or not, but I used it as such.

Also, I just guessed that there would be an income field and populated it. Example attached.

Good Luck,

MSalvage

Pivot Table Attempt.yxmd

ssubra60

Thank you so much for your solution. Would you mind sending your solution in a version compatible with 10.6? Is this hard?

I am new to Alteryx and my company has not recommended the 11 use yet.

If it is too hard, please let me know.

Thanks,

Sri

pcatterson

This is a bit arduous, but I think it gives you the basics on how to transpose data. There is nothing in the source data to tell me how the year is determined, so I left that undone:

PivotParse.yxmd

ssubra60

This solution addresses the problem I had of identifying the new months and transposing them. I am hoping I can pass it through the original file to see if I can replicate the flow for the actual file. Thank you so much!

ssubra60

Hi @cpatterson,

Thanks for the solution. The alteryx seems to work half way till you get to "jan,aud" "jan,cad" but when using cross-tab to create the pivot table, the income statement lines are lost in the "in-number" parsing. Would you it be possible to help to fix this problem?

Thanks,

Sri

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703