Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to add a date and job id field before insert to table?

  1. #1
    Join Date
    Oct 2008
    Posts
    28

    Unhappy How to add a date and job id field before insert to table?

    I have a text file I want to insert into a table. I use a Text Input.

    Employee First Name, Employee Last Name
    Smith, John
    Feinstein, Jerry

    Then I want to add a column for job_id. After this is done then I will Insert/Update a table.

    Employee First Name, Employee Last Name, job_id
    Smith, John, 2544
    Feinstein, Jerry, 2544

    Now with the job_id I use "Add Sequence" to get the next job number (e.g. 2544) from the database.

    My problem is how do I join this sequence (i.e. 2544) and attached this new job_id to the existing stream.

    I tried using a "Join Rows (Cartesian)" and that seems to only work for text input when my "Add Sequence" comes from a database.

    Please help!

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Text input file to your add sequence... when add sequence step receives input it will copy that input to is output while adding the sequence. Exactly what you need I think.

    Regards,
    Sven

  3. #3
    Join Date
    Oct 2008
    Posts
    28

    Default

    Not quite. I tried that before. Text Input->Add Sequence->Text Output. What happens is the rows would increment by one for every row. This is useful for inserting an automatic primary key.

    Employee First Name, Employee Last Name, job_id
    Smith, John, 2544
    Feinstein, Jerry, 2545
    blah, blah, 2546
    blah, blah, 2547

    But what I am trying to do is similar to "Add Constant" but except it needs to be a "Add Sequence". And I put the "Add Sequence" step as a parallel step because I cannot put this in a chain. Again the sequence will increment itself by one every time you call it.

    What I want is:

    Employee First Name, Employee Last Name, job_id
    Smith, John, 2544
    Feinstein, Jerry, 2544
    blah, blah, 2544
    blah, blah, 2544


    I also tried it the other way around: "Add Sequence"->"Text Input" just so that it only gets the latest sequence only ONCE (not 2000 times per row) but am getting an error.

    Any more advice for me please?

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Put a generate row (for 1 empty row) before the add sequence, then a cartesian join.

    Regards,
    Sven

  5. #5
    Join Date
    Oct 2008
    Posts
    28

    Default

    Yes this works. The solution is "Generate Row -> Add Sequence -> Join Row"

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.