Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: How i can add a 0 to a string

  1. #1

    Default How i can add a 0 to a string


    i have an excel input step.
    In one column field i have strings with length 5 and 4.
    So i need to add a 0 to the strings with length 4.

    How can i do this ? Can I do this with the format options ?

    Another problem i have is that some string fields are empty so i must set a 0 instead null. How can i do this?
    Last edited by KettleFan; 04-07-2009 at 02:59 AM.

  2. #2


    You could certainly do it using JavaScript. If your value is fully numeric and you want a leading 0 then you could also do it with the formatting options, probably with a format string like "00000".



  3. #3


    Replacing empty fields can be done with the Value Mapper step by leaving the Source value field empty.

    Btw., why do you have everything in string columns when it all seems to be numerical data?



  4. #4


    Thanx a lot

    I have change this to number field. So i can use the format options.

    Now 2 other questions.

    1.I use the Value Mapper to Query 2 columns
    if column 1 is null then take column2

    2. In SQL i have follow order : substr(column1,2,5)
    How can i convert it to my transformation? With step ??

  5. #5


    Sorry, I don't understand your questions... you might have to talk in complete sentences, otherwise I am too stupid to get it.

  6. #6


    OK sorry,

    i have an excel input step with several columns.

    1.So i must check column1 if it is null, when it is null then take column2 and write into column3. if column1 is not null then write column1 into column3.
    SQL = insert into test select nvl(column1,column2) from source
    I hope this is now clear to understand. Can I solve this problem with the Value Mapper or an other step?

    2. In column5 i have a number with length 6. I want to cut the first number. In SQL it would be substr(column5,2,5)
    Which step i can use for?
    Last edited by KettleFan; 04-08-2009 at 02:25 AM.

  7. #7


    Yes, now I get what you mean : )

    1) This case cannot be solved with the Value Mapper but with the Calculator step. The calculator step always creates a new column, so you don't need to have a third column before going into this step.
    In you example you would have to fill in these fields:
    New field: test
    Calculation: NVL(A,B)
    Field A: column1
    Field B: column 2
    Value type: whatever your value type is

    2) This can be done using JavaScript. Here is an example using your column5 and assuming it's a number type:

    var dummy = str2num(substr(num2str(test, '#'), 1, 5));

    The "#" in there is the number format to use when converting it to a string for using substr. Make sure you turn the compatibility mode off. Also you need to create a new column in the JavaScript step, in my example it's called dummy. For that you have to fill in two fields in the table at the bottom:
    Fieldname: dummy
    Type: Number

    I think in theory this step should be able to overwrite the old column (test = str2num(substr(num2str(test, '#'), 1, 5));) but that seems to be buggy, so creating a new column is better.

    All the best,


  8. #8


    Ah really nice !

    Thank you for this detailed Explanation ! It works fine.

  9. #9


    I must correct my last answer, the calculator step don't works.

    New field: test
    Calculation: NVL(A,B)
    Field A: column1
    Field B: column 2
    Value type: string

    In column1 is data and nothing. In column2 is full data.

    So the NVL function should be check if is null in column1 then take the data from column2. If column1 cointains data then take the data from column1.
    In the new field test it must be contain only data !

    The result i get only the data from column1 equal it contains. I dont understand this !

    I use the Excel Input Step and not Table Input, i think column1 contains not is null values and the nvl function failed.

    What can i do ?
    Last edited by KettleFan; 04-14-2009 at 03:43 AM.

  10. #10


    Further ideas ?

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.