Raw data to Pivot table

[ssubra60]

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

[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

[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 · Accepted solution]

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:

[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!

[MSalvage]

@ssubra60, 

Actually I do not know how to do that. Sorry about that... 

-MSalvage

[MSalvage]

@ssubra60,

Decided to google it and look at that there is a post describing how to do adjust it to your version. 

Hope it works out.

-MSalvage

[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

[pcatterson]

I'm not clear what isn't working.

Could you fill in your before and after with hypothetical data so I know where you want it to all end up?  You left the table blank and I had to imagine a likely scenario where things would be.

[ssubra60]

Hi @cpatterson

This is the file with pseudo data and what we need is a pivot table by month, currency, income line the dollars i.e

So each cell will be a separate line in the pivot table with its dimensions as row ad well,

For example- the vehicles cost in AUD for Jan 2017 IS 300 then we have:

Jan 2017            AUD         Vehicles Cost       300

Jan 2017           AUD          Revenue               400

Please see attached.

Thank you so much!!! Really appreciate it.

Thanks,

Sri