How to handle currency symbols?

[adam nealisParticipant]

I’m surprised I haven’t met this problem before.

I have some data, and there is an “Amount” column, which contains, e.g.

$1.33
-$0.19

Datagraph does not like this column and I can’t find an obvious way to treat the data in this column as a number.

The data are supposed to come from an exported file, which will be updated, so I don’t want to manually process the column.

What are my options?

[Jean-Yves Le StangParticipant]

Hello,

I have not tested but I suggest you remove the “$” selecting the concerned columns and using the “Edit/Find and Replace” menu option, then turn the column to a number column.

This is however a “manual” process although, all data are processed in one operation. If you want a fully automated process you can proceed as follows, in four steps, assuming that imported data is a string (Text Column) beginning with “$” for positive values and “-$” for negative values.

Step 1 : use the “Number from Text” Command from the “Other Menu” drop down to generate numbers from the imported column. This will extract the numbers (“ProcessAll” column), however, all values will be signed positive.

Step 2 : Extract only negative values using the same “Number from Text” command with different setting (“ProcessNeg” Column).

Step 3: test wether the “Process” values are negative (TestNeg column)

Step 4 : Set the final value according to  the sign of the value (Value Column)

I could not find any solution from the “Import Special” parser but there may be one ? Also may be some more elegant solution can be generated using the “Mask” function ?

May be the Application or DataBase your data come from may export unformatted numbers ?

I hope this is helpful.

[davidModerator]

The next beta will skip over the $ signs during imports. The current version only does that when you paste in data.

The special importer option has that as an option when you import as well. It is tucked inside a settings for the number column. The current version does not do the right thing for -$0.19, but the next beta does.

[Author]

Posts