Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: add decimal to a number

  1. #1
    Join Date
    Oct 2013
    Posts
    18

    Default add decimal to a number

    So I feel like this a really basic question but I cannot figure it out!

    I have a translation that takes a fixed width file and rearranges a bunch of the fields. Five of the fields are numbers and need a decimal added at certain points, but I cannot figure it out. I've tried adding a precision and I've tried using the meta-data tab to specify the exact format I need but it looks to be appending the decimals and zeros to the end instead of just going however many spots in and placing a .

    I've attached an example and my transformation but basically what I cant figure out is how to tell Kettle to turn a number such as 0000000006272000 into 0000000006272.000. I feel like it has something to do with using a 0 or a # in the format cell of the meta-data tab but everything I've tried doesn't work!

    Thanks
    npc_text_file_fixed_width.ktr
    Test_output.txt
    test_input.txt

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Use the calculator, and divide it by 1000.
    Remember that a number doesn't have leading 0's... A string does.

    So your input format would be 0 (tells it that is has to have at least one digit)
    Feed that to a calculator step (configure it to divide by 1000)
    Output would be as you are asking for.

    HOWEVER:
    Input 0000000006272123 -> 6272.123
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Oct 2013
    Posts
    18

    Default

    Yeah I was thinking of that as a workaround. I just thought there would be a more elegant way to approach it and couldnt figure out what I was doing wrong with the format cell.

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    If it's a string, you can do a substring and concatenate with a DOT.
    And then convert into a number using the select values step.
    -- Mick --

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.