Transposing Data

[PPNU]

I have an excel template for various regions. I want to read it through Alteryx and store the data in database.

I'm new to Alteryx, so I feel difficulty how can a new columns would be created for region and last date updated from the data in template.

Secondly, I want data store in columnar structure , means category which is in row in excel would be in column against which data would be populated for the category to make it easy for filter data on category level.

Please find sample data attached here. 

Can somebody recommend something

 Thanks in advance

[OllieClarke · Accepted solution]

Hi @PPNU 

That wasn't the most straightforward challenge, but here's a workflow that does what you want (I think):

as well as Cross-Tabs and Transpose tools, I also needed a multi-row formula tool and some Joins (along with an append field). 

I'd recommend checking out the learning resources available on the community Learning Paths, Interactive Lessons and Weekly Challenges as well as the documentation/example workflows of the tools in Designer itself. Between those resources you should be flying in no time. 

Hope that helps,

Ollie

[PPNU]

Hello @OllieClarke ,

This is working fine with one of the sheets. I have different excel sheets for different regions, saved under one folder.

I want them  to process them together. So I made some changes in input tool but its taking data from one sheet only not from all.

[OllieClarke]

Hi @PPNU 

Here's an article that goes through how to input multiple sheets: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets... 

[PPNU]

Thanks @OllieClarke ,

One issue is for string values in cross tab we have only functions to select, Concatenate, First and Last.

When I'll process data for multiple regions, what could be the possible alternative to get all markets and date.

[OllieClarke]

@PPNU since you're reshaping the data without aggregating it, that shouldn't be a problem. What you will need to do is input the file/sheet name with each sheet (option 5 in the data input tool) and then group by this field in all cross-tabs and have it as key field in all transposes. You should also add this as a join clause, and finally replace the append fields tool with a join tool that just joins on the file/sheet name. This should mean that each sheet's data stays separate. 

I've attached an updated version of the workflow that uses the filename (although as it's just connecting to 1 sheet it's a bit unnecessary)

[PPNU]

Hello @OllieClarke ,

when we're filtering header, there is a filter to select record ID. 

So if we're choosing record ID=1 it filters data for one sheet only and hence therefore, not getting data for multiple sheets at the end.

[OllieClarke]

Ah @PPNU , sorry forgot about that. 

Instead of using the Record tool, you'll have to create your own with a multi-row formula that is grouping by [filename]. This will restart the recordId for each sheet, so everything can then go on.

Screenshot and updated version below 🙂