Formatting from excel in Alteryx numbers and strings

[Asimkz157]

Hello all and thank you in advance for any help you can provide.  I have a excel workbook which contains multiple sheets and contained within those sheets are string data type and numbers.  Ideally I would like to have everything nice and formatted in column and rows or if not format the numbers at 2 decimal places. For example the tons and team tons from the "T Name" column would be 1.00 or 22.00 and everything else should be formatted in currency $$$.$$.  I also have percentages, "balloon percentage" would be a percent.  

I was also trying to get the months from F4, F5 and so on.  It's contained in the same column as numbers so converting it to a double data type would remove the month names.  Attached is a sample of my workflow.  My end goal is to convert each statement for each individual user into a PDF and sent by email. 

[mbarone]

You can change data types with the Select tool.   You can split out fields into new columns using the Text to Columns tool.  You can use a Formula tool to change a numeric field to a string field and add a "$" before it (there isn't a "currency" field type in Alteryx).  Outputting to PDF and emailing would require both the Render tool and either the Email tool or running an Event upon workflow completion.

There are lots of free sources in the Academy section of the Community to help you get started building workflows like you describe.

[Asimkz157]

The workflow I'm working with is not as simple.  I can't use the select to change data types because I have name of the month and measure values in the column itself, changing it to numeric will exclude the string values.  Not sure how text to column would work in this scenario as none of the columns need to be split.  

[mbarone]

It might be difficult to find someone willing to build your workflow for you.  Most everything you're asking is pretty basic Alteryx functionality.  Again, I'd suggest availing yourself of the various free resources in the Academy section of the community and maybe coming back with your attempt at the workflow using some of the various tools.

[Asimkz157]

I think you're misunderstanding the question, I'm not asking for anyone to build out the workflow, but my question is how do I extract the month names from columns that contain measures and align them to each month corresponds to a specific measure type.  I don't think a select, formula or text to column tool can accomplish this.  I checked and researched and i'm unable to find a solution  

[mbarone]

Sorry if I misunderstood.  What do you want the final output to look like?  Workflow will probably involve transpose and/or cross tab tool most likely.  But maybe if I could see what the final output should look like that'll help my understanding.

[Asimkz157]

No worries and sorry if I was unable to explain my problem accurately. 

Ideally, it would be nice to use name of the measures from the 'T Name' column and move them into their individual columns and also have a column for the month name.  It's hard to explain using words, if you take a look my sample workflow that's the current state the report is in and attached is a picture of how I would like the new report to look.  If this is not possible, how can I format the numbers for the Tons measure into business friendly values at 2 decimal places and use $ for currency values?  I tried using the crosstab tool and transpose tool and it's leaving out values and/or column names.  Maybe I not configuring it properly.  Thank you for your time. 

[mbarone]

Oh I see.  Yes, I was way off on what you were asking.

It's going to be very tough to get it like that in a dynamic way.  Will the columns and rows always be the same (meaning the blank rows will always be in the same place)?  Would really have to take some time to mess with it.  But you're right - it's a difficult task.  

For the Tons values, you could use a Multi-Field Formula Tool for all the F fields, use a formula and update the field type to v_wstring:

[mbarone]

Actually, move the '$' over a bit so it only displays if it's for the Tons value.  So it should be:

ToString(IIF([T Name]='Tons','$'+ToString(Round(ToNumber([_CurrentField_]),.01)),[_CurrentField_]))

[Asimkz157]

Thank you, that actually worked on the Tons column.  I had to remove the $ sign but I was able to convert the numbers to two decimal places.  Can I nest formulas with this tool.  I have other measures that need $ sign so I tried using ToString(IIF([T Name]='Tons',ToString(Round(ToNumber([_CurrentField_]),.01)),[_CurrentField_]))Else ([T Name]='Customer Contribution','$'+ToString(Round(ToNumber([_CurrentField_]),.01)),[_CurrentField_]) End but this didn't seem to work.  Is my syntax wrong or do I need to use a new tool for each measure?