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

Data format in output as "Accounting" type

amyfshe
5 - Atom

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!

7 REPLIES 7
estherb47
15 - Aurora
15 - Aurora

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!

Estherimage.png

ddiesel
13 - Pulsar
13 - Pulsar

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!

 

ddiesel
13 - Pulsar
13 - Pulsar

Here is an example of a template file.

amyfshe
5 - Atom

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

amyfshe
5 - Atom

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

amyfshe
5 - Atom

@ddiesel Awesome idea! I will be implementing this for myself. It's a little harder if I share this workflow with my colleagues to have them use the same template, but this is such a wonderful idea for myself. Thank you!!

ThizViz
11 - Bolide

Genius. I will undoubtedly make use of this technique in the future!

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Labels