Formatting from excel in Alteryx numbers and strings

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.

SampleWorkflow.yxmd

Workflow

Datasets

Reporting

Output

Tips and Tricks

Custom Tools

Common Use Cases

Preparation

Accepted answers

mbarone

Yes, you can nest. Looks though like you're combining boolean IIF with IF/THEN/ELSE. I tend to stick with one or the other (boolean is my favorite since less typing: IIF(x,y,z) = "if x, then y, else z").

With nesting though, you have to leave the case that isn't covered by any of the nests for the very last. So in boolean it'd be like this (I added some spacing to make it more readable, and also I totally forgot that you don't need the Round function because if you change it to Number, and then a String, you can make use of the "numDec" and "addThousandsSeparator" arguments within the ToString function - see ToString

ToString
(

IIF([T Name]='Tons',ToString(ToNumber([_CurrentField_]),2,1),
IIF([T Name]='Customer Contribution','$'+ToString(ToNumber([_CurrentField_]),2,1),
[_CurrentField_]
))

)

All comments

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:

2021-08-17 15_07_07-Alteryx Designer x64 - SampleWorkflow.yxmd_.png

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?

mbarone

ToString
(

IIF([T Name]='Tons',ToString(ToNumber([_CurrentField_]),2,1),
IIF([T Name]='Customer Contribution','$'+ToString(ToNumber([_CurrentField_]),2,1),
[_CurrentField_]
))

)

Asimkz157

Thank you, that works like a charm. I still have a lot to learn on the available tools and proper use of syntax.

mbarone

Welcome!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703