Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

HelenL
8 - Asteroid

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 

 

 

10 REPLIES 10
patrick_digan
17 - Castor
17 - Castor

@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

HelenL
8 - Asteroid

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

patrick_digan
17 - Castor
17 - Castor

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

HelenL
8 - Asteroid

Thanks Patrick,

 

I successfully used your method 2 - the date time parse tool. Once I had it I was able to find the Help page for Specifiers https://help.alteryx.com/11.0/index.htm#Reference/DateTimeFunctions.htm which was very useful. Thanks for providing the three alternatives - I've filed your workflow away in my Alteryx learning resource folder for another day. 

 

Now I've just spent another fruitless hour or so trying to add the File name. This data set is the the "Responsibility" section of a website. I want to combine the data for many website sections together (Responsibility, About Us, Investor relations etc) and have the section name e.g. "Responsibility" as a Dimension in Tableau. 

 

I know how to use "Output File Name as Field" in the Input tool. However, I can't manipulate this new field through the Transform / Crosstab combination.

I've tried using a parallel "Transform / Crosstab" combination but trial and error did not yield the solution. I'm just not grasping some basic concepts here. It's very frustrating :(

 

I want the final fields to be Record, Filename, Date added, 2.2. 3.4. (Then I'll want to rename the "Filename" header to "Section" and shorten the filename to the section name e.g. "Responsibility". Of course I can do this is Tableau but I'd rather do it properly in Alteryx.) 

 

 

Regards,

Helen

patrick_digan
17 - Castor
17 - Castor

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

HelenL
8 - Asteroid

Hi Patrick,

Thanks again, it works great. 

 

But something puzzles me. Why is the Date Time Parse tool picking up "Section" to convert. Why doesn't "ID" appear in the list to select? How come it works despite this?

 

I was worried about the DateTime conversion error and that file name still appears in record 13. But I suppose it doesn't matter. I could unselect record 13 I guess in the final step. 

 

Regards,

Helen

patrick_digan
17 - Castor
17 - Castor

@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.

HelenL
8 - Asteroid

Thanks for the explanation. I can now see when it changes.

HelenL
8 - Asteroid

Hi Patrick,

 

You wrote:

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

So first it finds the underscore "-". 

 

I had removed the name of my client company from the file name. So actually the file names are like this:

Company.com_-_About_Company_error_change_12_month_18-Apr-17.xls

Company.com_-_Responsibility_error_change_12_month_12-Apr-17.xls

 

So the "bunch of stuff that doesn't matter" follows the 3rd or 4th underscore. 

 

From Alteryx Help I followed a link to http://www.boost.org/doc/libs/1_62_0/libs/regex/doc/html/boost_regex/syntax/basic_extended.html.

 

Is the answer in the "Repeats"operators?

 

Regards,

Helen

 

 

Labels