Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How to insert a constant value into a Table output.

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default How to insert a constant value into a Table output.

    Hi,


    I am having some problems trying to figure out how to define a Constant to be used for populating a field within a Table output.



    I have a Text File Input step which connects to a Select Values step which connects to a Table output step. The Text file input contains 9 fields and the Table output contains 10 fields. I wish to move the 9 fields from the file and create one Constant value for the table output's 10th field. How do I create this constant value to be inserted into the table output? How do I define the Select Values so that the table does not try to remove its 10th column? Is there a better way to do these simple translations?



    Thanks,



    Jason Toyne

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How to insert a constant value into a Table output.

    Possible solutions:

    1) Add a Calculator step (Transform category)
    Use Calculation: "set field to constant value A"
    Specify metadata, standard conversion rules apply:
    Date: yyyy/MM/dd HH:mm:ss.ppp
    Boolean: Y/N, True/False

    2) Use a javascript piece of code: (Java Script Value step under Transform category)

    var constantValue = "A string";

    3) Generate 1 row of data with Row Generator (Input category) and use a Join Rows (in Transform Category)
    (Don't specify a condition)

    Good luck!

    Matt

  3. #3
    Join Date
    Dec 2005
    Posts
    3

    Default RE: How to insert a constant value into a Table output.

    Matt,

    Thanks for the quick response.

    When I try to use either the calculator or the javascript I run into a problem because I need to have input values for these steps. Which for this constant field I do not have. Also, there are a few other fields which I don't want to insert at all into the output table. In order to cause the output table not to remove these fields I need to specifiy them somewhere. Any ideas?

    Jason Toyne

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How to insert a constant value into a Table output.

    Hi Jason,

    The whole idea behind Kettle is to leave rows as they are as much as possible.
    This means that you just give all fields as input to Calculator.
    Then just one constant value is added to the rows. It's as simple as that.
    It gives the best performance if you really think about it. (no assembly of fields)

    The whole "Constant" idea of for example Oracle Warehouse Builder is based on programming models like PL/SQL. No program model is used in Kettle, it's all meta-data.

    Select values/fields using ... the "Select values" step in the Transform Category.

    Basically you have the following steps in your example (as far as I know):
    1) Text File Input : read fields from a text file
    2) Calculator : add a constant value
    3) Select values: select the values you want
    4) Table Output: stores the data in a table

    You could add database lookup steps etc in between, but unless you do special things, it's all serial: one step linked to the next.

    I hope this helps you out.

    Good luck!

    Matt

  5. #5
    Join Date
    Dec 2005
    Posts
    3

    Default RE: How to insert a constant value into a Table output.

    Hi Matt,

    Thanks for the explanation. I tried it and believe it will work. However, I have ran into another item I don't understand. I have a Text File Input --> Select Values --> Calculator --> Table Output. If the Text File Input contains any fields that have null/no values then the Select Values step throws an exception. Is there a way around this as our Input files will often have null values.

    Thanks again,

    Jason Toyne

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: How to insert a constant value into a Table output.

    Hi Jason,

    Null values shouldn't be a problem and if an Exception occurs then it should be treated as a bug.
    I haven't had it before and almost every transformation uses it, but that doesn't mean anything, the possibilities to check for are almost endless.

    Can you tell me a bit about the data type that's throwing the exception?
    Perhaps some debugging output?

    BTW, if there are no values in the row (empty row) then that's a whole different story.
    You can check 'No empty rows' in the Text file input step to avoid those.

    Thanks,

    Matt

  7. #7
    Join Date
    Apr 2009
    Posts
    12

    Default Use this to set processed flag

    I used this to set processed flag after each row is inserted. Works great.

    Thanks

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.