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

Alteryx automatically converts string (£45.50) to number (45.50) while importing file?

anks_joi
6 - Meteoroid

Hello people!

 

I am importing a .xlm file that has value column with currency eg. £45.50 or 56.70 EUR. When I import this file using Input Tool, Alteryx automatically converts these values into numbers and removes the £ sign and EUR. I would like to keep it as is and don't want it Alteryx to convert values to numbers because I need to see which values are £ and which are EUR. Can someone please help with this query?

Thanks in advance!

6 REPLIES 6
vizAlter
12 - Quasar

Hi @anks_joi — Can you attach sample data?

 

(Looks that digits are not in String data type in your input file.)

anks_joi
6 - Meteoroid

@vizAlter - Thank you for your response. Unfortunately I can't attach data as it is confidential client data. I think you are right, the digits are not in string data type it is "Custom". I have 52 such excel files with half a million rows of data in each file. Is there a way I can convert the number to string in Alteryx and still have £ and EUR in the column?

vizAlter
12 - Quasar

@anks_joi — Okay, then once you bring the data to Alteryx workflow and use a Formula to put the Currency sign or abbreviations.

for example to create a new field or update the [Amount] filed:   "$"+[Amount]

 

If the layout of your each file is the same, then you can bring all of them in one shot, like this screenshot:

vizAlter_0-1600283625745.png

 

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

anks_joi
6 - Meteoroid

@vizAlter - My column in excel has £ and EUR both in same column hence I can't have just one currency by using Filter tool. I would like to bring in the data as it is, so that I can convert EUR to £ and have everything as £ in one column. Below is the column in my .xlm file, for eg.:

 

Value Retail Loss

-£ 21.20
£ 1,092.50
£ 1,134.00
-£ 21.20
-21.00 EUR
-21.00 EUR
-20.99 EUR
-20.98 EUR
vizAlter
12 - Quasar

@anks_joi — Let me know that what is mentioned for "Custom" format for your Currency column, provide that too.

vizAlter
12 - Quasar

@anks_joi  —  Try this solution..., hope it will be helpful, if not let me know.

 

In Formula tool:

 

"£ "+ REGEX_Replace([Value Retail Loss], "([^0-9,.-])", "")

 

 

vizAlter_0-1600288370638.png

 

 

Please mark it "Solved" or "Solved" with a Like if it resolved your query. This will help other users find the same answer/resolution.  Thank you.

Labels