Visokio Forums

aknotts · February 15, 2012 3:55AM

Ver 2.6b832 64-bit

I have a model in DataManager where I have loads of files with different locales. I need to append them all together to the decimal fields are all recognised as numbers and I want to format them in the UK style ie using a decimal point as a decimal point rather than a comma as a decimal point. For example:

Netherlands
Item, Amount
Box, 15,25 (meaning 15.25)
Car, 15.000,27 (meaning 15,000.27)

UK
Item, Amount
Box, 9.99
Car, 8,250.34

Appended I want the output to be
Source, Item, Amount
Netherlands, Box, 15.25
Netherlands, Car, 15,000.27
UK, Box, 9.99
UK, Car, 8,250.34

Aggregated I want the output to be
Item, Amount
Box, 24.24
Car, 23,250.61

What is the best way to do this instead of importing as text, searching and replacing commas for points?

Thanks

Andy

steve · February 15, 2012 9:39AM

When you import a text file, you should choose the Locale field for each source, as appropriate. For example, if importing a French file containing numbers such as "10,5", and you leave it as "UK" or "US", this will import as "105". After changing "Locale" to "French", it imports correctly as "10.5".

In the DataManager file source block for a text/csv/tsv/etc. file, scroll down to "Locale".

Alexander · February 28, 2012 9:26AM

Hi Steve,

Can we select these "locale" option in database connection as well?

I do hope so. Because now I am using a database connection to create a .csv, which I import with the "Locale" settings.

Regards,
Alexander

steve · February 28, 2012 9:42AM

Locale is only relevant when interpreting text values as a date or number.

Databases are imported using the data type defined in the database. So if your database has a numeric column, it will be imported as a numeric value in Omniscope; there is no way for the locale to affect this.

Note that numeric values in Omniscope will be displayed according to your "data locale" settings - see "Settings > Data locale". But this is only a display option; the underlying data has the same value.

If, unusually, your database has a *text* field which actually contains numbers, Omniscope will import this as a text field too. You can use Field Organiser to convert the field to numbers. Expand the relevant field in Field Organiser to customise the locale used for this conversion.

Visokio Forums

Welcome!

Categories