Floating points not recognized correctly

Hi guys, I'm working with VDP 7.0 and I have [this CSV file](https://openflights.org/data.html#airport) that amongst other airport-related data contains latitude and longitude information. Here's a sample row of the dataset so you don't have to download the whole file: ``` 34,"Castlegar/West Kootenay Regional Airport","Castlegar","Canada","YCG","CYCG",49.2963981628,-117.632003784,1624,-8,"A","America/Vancouver","airport","OurAirports" ``` Seems reasonable enough to me to convert fields 7 and 8 (lat, lon) to float, double or even decimal, but when doing so I seem to lose the decimal point all together, resulting in _492963981628,00_ to be shown. The point instead is correctly displayed when querying the same view but with such fields typed as originally as text. Things I tried: - converting the fields using the Edit tab of the base view, from the Field Type column dropdown - creating a new Selection view, using CAST with such fields Used both methods trying out others than my own locale (it_euro), which uses commas instead of dots for floating point notation (changed it in _Tools_ > _Admin Tool Preferences_ > _Locale_, even ticking _Internationalize query results_). CSV was imported locally using _,_ as column delimiter, _\n_ as EOL delimiter, automatic encoding detection and _Ignore matching errors_ ticked. Is there anywhere else I can change the locale? Am I doing something wrong elsewhere? Thanks in advance!
user
19-06-2019 09:16:25 -0400

3 Answers

Hi, To handle decimals explicitly, you can use round() function. We use it most of the time as our consumers have different requirements for each attribute https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/arithmetic_functions#round You can use round() at view-> edit->output-> [field under 'Field Name']. Hope this helps.
user
19-06-2019 13:50:45 -0400
Hey, thanks for your answer! Unfortunately that seems not to be what I need. Round needs a numeric input, and whenever I go and convert the field to numeric I lose the floating point all together (as in the example, when the source and the text field have a value of 49.2963981628 whenever I convert it to a floating numeric i get 492963981628,00 and having different precisions I cannot even use a division with a fixed number that will allow me to get to the correct result). Am I missing something or do you have any other clue?
user
20-06-2019 08:28:22 -0400
Hi, I was able to cast the latitude and longitude columns from text to double by creating a new map and replacing the double decimal separator as ',' and the double group separator as '.'. For example, ``` CREATE MAP I18N i18n_us_pst ( 'country' = 'US' 'datepattern' = 'MMM d, yyyy h:mm:ss a' 'doubledecimalposition' = '2' 'doubledecimalseparator' = ',' 'doublegroupseparator' = '.' 'language' = 'en' 'timepattern' = 'DAY' 'timezone' = 'PST' ); ``` This new map should then be selected as locale following the below steps on Virtual DataPort Administration Tool. > Tools > Admin Tool Preferences > Locale > Select the Internationalize query results check box. After applying this change and casting the values from text to double converts the decimal separator as per new mapping and it produces the expected output. You can refer [Managing Internationalization Configurations](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/advanced_characteristics/creating_new_internationalization_configurations/creating_new_internationalization_configurations) Section of Advanced characteristics section of Virtual DataPort VQL Guide. For more details on Admin Tool preferences you can refer [Locale](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/installation_and_execution/launching_the_virtual_dataport_administration_tool/tool_preferences#locale) section of Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
20-06-2019 08:47:15 -0400
You must sign in to add an answer. If you do not have an account, you can register here