Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Another Beginner Question: Add Leading Zero/Remove Trailing Zero

  1. #1
    Join Date
    Nov 2013
    Posts
    16

    Default Another Beginner Question: Add Leading Zero/Remove Trailing Zero

    Hi Everyone,

    Sorry for asking another fairly simple question, I've searched everywhere and still haven't found a solution. Here's what I am trying to accomplish in Kettle 4.4 CE (I'm going from DATABASE --> TEXT FILE), hopefully someone can help:

    This is the pipe-delimited text file output I have right now:
    PDT:2014-04-01/06:27:07.393 |ABCD|XX|0809123450|CrProp|amount:1.21,currency:CAD (example #1)
    PDT:2014-04-01/06:27:07.393 |ABCD|XX|80912345|CrProp|amount:1.21,currency:CAD (example #2)

    This is the text file output I want:
    PDT:2014-04-01/06:27:07.393|ABCD|XX|080912345|CrProp|amount:1.21,currency:CAD

    Which Transformation Step (or maybe other steps) do I use to Add or Remove Leading/Trailing Zero's? I just find it difficult to work with numeric values, strings are much easier. That said, hopefully I don't have to convert them string. Any help would be much appreciated!

    David.

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

    Default

    By definition, a whole number doesn't have leading 0s.
    By definition, a trailing 0 on a whole number is important (1 is not the same as 10 when dealing with numbers!)

    Your "Number" is actually a string with only numeric characters.

    So convert it to a string, and deal with it accordingly.

    HOWEVER, how many 0s on the tail end can validly be removed?
    Is 80912345 = 809123450 = 8091234500 = 80912345000 ?

    You seem to be trying to replicate either a report or a Fixed-Width File for some unspecified reason - These might be easier to accomplish with Reporting step

  3. #3
    Join Date
    Nov 2013
    Posts
    16

    Default

    Thanks for your reply.

    From my source databases, they are usually 8 (with no leading zero) or 10 (with extra trailing zero) digits that represents the financial institution number. However, I need them to be exactly 9 digits so I can do a Database Lookup to another table and map them.

    I guess one option is to use the 'Select values' transformation step and change the meta data to String. Then do use the 'String operations' step and go from there? To answer your question, yes 80912345 = 0809123450 = 080912345 (those are the only 3 possible formats). Anyways, thanks for your help.

    David
    Last edited by zzhang33; 04-08-2014 at 08:09 PM.

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

    Default

    Quote Originally Posted by zzhang33 View Post
    I guess one option is to use the 'Select values' transformation step and change the meta data to String. Then do use the 'String operations' step and go from there?
    Yup, that's pretty much what you have to do... Your "Number" is not actually a number (even it is being stored in the DB as a number) but rather it is a string - Financial Institution Identifier.

    You could go through a bunch of convoluted processing to determine if the last digit is a 0, and if so divide by 10, but that doesn't help with your leading 0 issue. A leading 0 can only exist in a string.

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.