1

I parsed a CSV file, which contains currency_exchange_rate column with values with different decimal places, for example:

  • 3.6415
  • 0.896458
  • 1
  • 0.30376

I would like to have all the rates rounded to 4 decimal places, in case above it should look as below:

  • 3.6415
  • 0.8965
  • 1.0000
  • 0.3038

When changing the column data type to 'float' the 'format' option is greyed out. I checked the 'Directives' and 'Functions' chapters for some more information and there is nothing referring to the decimal places of a float value. I found another thread on StackOverflow regards custom transformations, but still I don't know if it is possible to round the values as I need.

Can you please let me know how to achieve the format I described above? Any input would be much appreciated.

markoo
  • 19
  • 3

1 Answers1

2

If you have already changed the column data type to float, you can use the options under Calculate (instead of Format) to do operations like round, multiply, divide, etc. Note that "Round" will round to the nearest integer, so you would need to do something like multiply and divide by 10^(number of desired decimal places) as well as rounding.

You could also define a custom transformation to do something similar. A simple custom transformation that takes all the values of a column named col1 and rounds them to two decimal places might look like this: math:round(col1*100)*0.01

For a more complicated (and perhaps more precise) custom transformations you can use a jexl expression. More information on custom transformations, including examples, can be found here: https://medium.com/cdapio/advanced-cdap-directives-c10569724da0

Yuki Jung
  • 21
  • 2
  • Thank you, finally I got the idea about first multiplying, rounding and dividing the float value, namely in case of let's say 21.34748798: 1) multiplying by 10^4 would give me 213474.8798 2) rounding would give as a result 213475 3) dividing by 10^4 would return 21,35 This solution however, allows me to cut more than 4 decimal places to maximum 4. In my case I would need to have fixed number of decimal places, so that having 1 as an input I need 1,0000 as an output. I checked the Medium article, but no findings so far. Do you have any idea? – markoo Jan 30 '20 at 07:29
  • @Marek If you change the type to double instead of float, you can use the following custom transformation to round to n decimal places and also get the trailing zeros: (math:round(body_6*10000.0))/10000.0 – Yuki Jung Jan 31 '20 at 01:03
  • I tested your suggestion and put following directive into action (having checked that the datatype is double): set-column currency_rate math:round(currency_rate*10000.0)/10000.0 And having 1 as an input I get 1.0 as an output instead of 1.000. I think this is fine for calculating value in other currency. In case of the need to keep format consistent I suppose it would be necessary to create user defined directive and store the column as string. – markoo Jan 31 '20 at 08:45