Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

XLS format drops zeros... runs fine in XLSX

Highlighted
8 - Asteroid

Hi all... I imagine the solution to this is over my head, but I'm going bonkers and need help  I created a workflow working from a worksheet in XLSX... turns out the source file is actually XLS.   Everything comes in as VWString and when I convert it to any numerical value of any kind to match the original data format t is dropping the values that are negative (in parenthesis) and making them null. The positive numbers don't drop.

 

Any suggestions?  Is there a tool to convert the negatives before I make numerical?  

 

Thanks!

 

 

Highlighted
12 - Quasar

Hey! I've had this before - I'm trying to think of what my solution was.

 

Does the data retain a specific data type in Excel?

Highlighted
Alteryx Certified Partner

Hi @andrewplaice ,

 

You need to replace the parentheses as these will not be recognised as numeric columns.

Use the formula tool (or multi-field formula) and use the following expression:

replace(replace([Field],"(","-"),")","")

 

This will give the following:

 

mceleavey_0-1591299028275.png

Hope this helps.

 

M.

 

Highlighted
8 - Asteroid

Thank you!  Worked perfectly... I was trying but kept getting lost in all the (),,"",)) 

 

Now that I see it in front of me, it make sense.  Appreciate it!

Highlighted
Alteryx Certified Partner

Ha.

Glad I could help!

 

M.

Labels