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

XLS format drops zeros... runs fine in XLSX

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

 

 

4 REPLIES 4
jacob_kahn
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?

mceleavey
17 - Castor
17 - Castor

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.

 



Bulien

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

mceleavey
17 - Castor
17 - Castor

Ha.

Glad I could help!

 

M.



Bulien

Labels