Data format in output as "Accounting" type

Hello - I've looked through past answered threads & tutorials on how to solve my problem but haven't been successful in finding a solution.

I have a workflow that has a report input where date field headers change every week. I am looking for a way to configure the output to change the data format of records that show "0" to show as "-" (dash). In Excel, the equivalent of selecting a record (or a range of records), right click --> Format Cells --> Number tab --> Accounting

The tricky part is because the date field headers change every week, if I were to put in a "If else" formula for every column, that formula would need to be updated every week to account for the new date column.

I attached the output report - Sheet1 is the current output, Sheet2 is the desired output. But the column headers change weekly so I'm having trouble finding a solution that takes changing columns into consideration.

Thank you in advance for your guidance & advice!

Alteryx Community Test.XLSX

Workflow

Reporting

Tips and Tricks

Common Use Cases

Best Practices

Preparation

Accepted answers

estherb47

Hi @amyfshe

You can try a multi-field formula tool, which looks at all of the columns, regardless of their headers.

Use an expression like: iif([_CurrentField_]=0,"-",[_CurrentField_])

and set the data type to String (otherwise you can't use a dash).

Just be sure to nest a tonumber() function around any of the remaining numbers in any tool if you want them to act as numbers.

Cheers!

Esther

ddiesel

Hi @amyfshe !

@estherb47 is spot on with the multi-field formula. I just wanted to add that I have struggled with this same issue, and I wanted to share a workaround that works for me.

First I create an Excel template that has a hidden "data" sheet. The main sheet (named "Formatted" ) holds all the formatting. Each cell on the "Formatted" sheet contains a formula that points to the corresponding value on the hidden "data" sheet. Then I execute the workflow to Overwrite Sheet (drop) to the "data" sheet. This allows me to control the formatting, print preview, headers/footers, add a logo, etc.

In my use case, I overwrite the same file each time I run the workflow, then the attachment is emailed out to the subscribed recipients but you could use a bat file to copy over a new template each run.

Hope that helps!

Data format in output as Accounting type ddiesel.yxmd

ddiesel

Here is an example of a template file.

Alteryx Community Test Template.xlsx

All comments

estherb47

Hi @amyfshe

You can try a multi-field formula tool, which looks at all of the columns, regardless of their headers.

Esther

ddiesel

Hi @amyfshe !

@estherb47 is spot on with the multi-field formula. I just wanted to add that I have struggled with this same issue, and I wanted to share a workaround that works for me.

Hope that helps!

Data format in output as Accounting type ddiesel.yxmd

amyfshe

THANK YOU both! I will try this today & report back. Really appreciate it!

amyfshe

@EstherB47 Thank you for the Multi-Field formula - it worked! However, the non-zero numbers are now String type... is there a way I can apply another multi-row tool that converts only those non-zero records into Numeric data types? I'm a little stuck on if that's possible.

Either way, thank you for the help! I am 99% there which is way farther than I was before posting!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703