How do I "transpose" Crownpeak xls download for Tableau?

I want to create a time series in Tableau.

The download .xls has

Rows: Quality checkpoints

Columns: Months

In Excel I would use Copy and then Paste Special to transpose. See sheet "DESIRED FORMAT" that I created this way. How do I build the workflow for this in Alteryx?

(I use the Transpose tool in workflows to reshape survey data for Tableau. So "transpose" seems to have different meanings.)

I also need to remove a row of NULL values.

I have attached a .yxmd file. I wanted to save as a packaged file but I don't see the option under "File" or as file type when I "Save as". https://help.alteryx.com/9.5/index.htm#ModulePackager.htm#Export_Workflow_Package

Helen

Responsibility_error_change_12_month_12-Apr-17.xls

Crownpeak data to Alteryx community.yxmd

Transformation

Accepted answers

patrick_digan

@HelenL Great questions! I've attached my updates.

@HelenL wrote:
How do I build the workflow for this in Alteryx?

I would use a recordID, transpose, crosstab and dynamic rename to get the same effect.

@HelenL wrote:
I also need to remove a row of NULL values.

I would just use the select records instead of the sample tool.

@HelenL wrote:
I wanted to save as a packaged file but I don't see the option under "File" or as file type when I "Save as".

It's hidden under the options>>>Export Workflow

Crownpeak data to Alteryx community.yxzp

patrick_digan

@HelenL

@HelenL wrote:
What's the easiest way to change the month to a date format? I found that simply changing Type to Date in a Select tool does not work. Guess I first need to get the String into a recognisable format. Could I use a Formula tool e.g to change "01 Nov 2016" to '01 November 2016'. Or is there a Date/time tool?

Do you need an Alteryx Date or a Tableau Date or just a better formatted Date?

I'm attaching a workflow trying to capture all of these possibilities. Working with dates takes some time to learn. Basically, you need to your ID field (which is a string) and "convert" it to an alteryx date. I've put in 3 different ways to do that which all yield the exact same result. The first is a macro that @Kenda put together that uses a multi-field formula tool. Your date "01 Nov 2016" is formatted as "%d %b %Y". The nice thing about the macro is that it handles multiple conversions (if need be) and overwrites the field (which avoids having an extra select tool). The second way is the date time parse tool. It uses the same format as the macro, but it can only handle one field (which is ok for this workflow) and needs a select tool after it (minor inconvenience). The third method is the formula tool using a DateTimeParse function. At this point, we've created an Alteryx Date: yyyy-mm-dd (2017-04-12 for today). Now if you really did want something like 01 November 2016, I've included the same three set of tools to convert the date back to a string in this format. Instead of %b, you would use %B.

Hope that helps!

Crownpeak data to Alteryx community.yxzp

patrick_digan

@HelenL It depends on what your tab names are going to be. I'm assuming that they will all be something like "<Section Name>_bunch of stuff that doesn't matter". In that case, I think we can use Regex to grab the section name before the underscore symbol. So here's what I did in the attached:

1) Changed the Input Tool to output the file name only.

2) Added a formula tool immediately after the input tool to calculate our section field:

Regex_replace([FileName],"_.*","") 

So it first finds the underscore "_". The . means any character at all, and the * means one or more. 
So .* together will find the rest of the field regardless of what text is in there. 
Then it replaces all that stuff with "", i.e. nothing. This should leave just your section name (like Responsibility in this case).

3) Add section field as a key field in your transpose.

4) Add section field as a group by field in your crosstab.

5) Deselect section field from the dynamic rename.

6) On your final select, you may want unselect the *Unknown box if you really only want 2.2 and 3.4. I can imagine a scenario where you get a new variable (like 9.3) and it will then flow through to your output.

Hope that helps!

Crownpeak data v2.yxzp

patrick_digan

@HelenL Great observation! So when you open the file (and before you run it), that tool is correctly configured to use the ID field; however, because of the tools being used (crosstab and dynamic rename), the metadata in this workflow is a little tricky (and I honestly don't know when/exactly why the metadata wouldn't persist 100% of the time). If it doesn't have the correct metadata, it displays that Exclamation point since you're selecting the ID field and it doesn't have a field called ID in its metadata (since it doesn't recognize the field names coming out of the dynamic rename at certain times). The dangerous part comes when you click on the tool to see what the issue is, and it will automatically change the selected field to section! At this point if you clicked save, it would now have the section field instead of ID.

So make sure you run it and select ID in the date time parse tool before saving your final version.

All comments

patrick_digan

@HelenL Great questions! I've attached my updates.

@HelenL wrote:
How do I build the workflow for this in Alteryx?

I would use a recordID, transpose, crosstab and dynamic rename to get the same effect.

@HelenL wrote:
I also need to remove a row of NULL values.

I would just use the select records instead of the sample tool.

@HelenL wrote:
I wanted to save as a packaged file but I don't see the option under "File" or as file type when I "Save as".

It's hidden under the options>>>Export Workflow

Crownpeak data to Alteryx community.yxzp

HelenL

Brilliant answer! I couldn't believe how fast you replied with the workflow. Can I really only give it 1 star? It deserves 5.

The RecordID, Transpose, Crosstab, Dynamic rename is a clever combination.

Thanks very much for providing your workflow. It took me a while to work though your solution (aided by lots of Browses to see what was going on). I had to refer to yours to get the config right on the Crosstab. So it was much appreciated.

What's the easiest way to change the month to a date format? I found that simply changing Type to Date in a Select tool does not work. Guess I first need to get the String into a recognisable format. Could I use a Formula tool e.g to change "01 Nov 2016" to '01 November 2016'. Or is there a Date/time tool?

Regards,

Helen

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703