Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Multiple inputs -> Single output

  1. #1
    Join Date
    Feb 2011
    Posts
    840

    Lightbulb Solved: Multiple inputs -> Single output

    I mentioned this on another thread, maybe I'm being too hasty, but I think this should be in a new one - specially if there's a solution, it'd be easier for someone else looking for it to find. I've exhausted every little bit of knowledge I have - and even some other attempts by trial and error, that all ended in error.

    I have several Table inputs, each with a different set of fields, and I wanted to kinda join them - hence the title, multiple inputs for a single output. Something like using a template table with all the fields declared.

    template: field1;field2;field3;field4;field5;field6
    table1: field1;field2;field3
    table2: field4;field5;field6


    Since table 1 doesn't have fields 4-6, it's rows on the final table would have them empty. Same with table2, it's rows would have empty fields1-3. Ideas?
    Last edited by joao.ciocca; 03-02-2011 at 12:41 PM.

  2. #2
    Join Date
    Nov 2009
    Posts
    687

    Default

    When both records have only one record you can use Join rows (cartesian product) and the output wil be one record as you want. This will not work in the input streams have multiple records. In your case when table1 has 2 records and table2 has 4. Then your output template has 8 records

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

    Default

    Hi Joao.
    I don't understand what you want to achieve.
    Can you make another example?

    Do you want to merge records together where they have the same ID but from different tables?
    Or do you simply want to append records form both tables into a new one?

    Mick

  4. #4
    Join Date
    Sep 2010
    Posts
    135

    Default

    If I understood well you want to do something similar to the example I've attached. In this case the output is a text file but your solution will be something similar to a Table Output step. Let me know if I'm wrong or I haven't understood what you want to achieve.

    Regards,
    Aitor
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2011
    Posts
    840

    Default

    almost @augarte, almost... I had reached that point, but it's not what I want still, "minor-major" thing I forgot to mention: some tables do have fields in common.

    table1: field1, field2, field3, field4
    table2: field1, field2, field5, field6
    table3: field2, field2, field7, field8

    output: field1, field2, field3, field4, field5, field6, field7, field8

    What PDI does with the Merge join is: if two fields from two different inputs have the same name, say table1's field1 and table2's field1, it changes the name of the second, so on the output, instead of filling output's field1 with table2's field1, it creates a new field called field1_1.

    I guess the example of selects and a "generate rows" to show the output, attached, could help.
    multiple inputs single output.ktr

    -=edit=- forgot to mention: no, none of the records from the different tables have ANY field in common. like, there's nothing like this:
    Table1: '1', '2', '3', '4'
    Table2: '1', '2', '5', '6'

    it NEVER happens. What happens is something like this:
    Table1: '1', '2', '3', '4'
    Table2: '5', '6', '7', '8'

    And the output should be:
    Row from Table1: '1', '2', '3', '4', , , ,
    Row from Table2: '5', '6', , , '7', '8', ,
    Last edited by joao.ciocca; 03-02-2011 at 09:51 AM.

  6. #6
    Join Date
    Sep 2010
    Posts
    135

    Default

    You say this:
    "What PDI does with the Merge join is: if two fields from two different inputs have the same name, say table1's field1 and table2's field1, it changes the name of the second, so on the output, instead of filling output's field1 with table2's field1, it creates a new field called field1_1."

    So I guess you want table 2's field1 to have the same value of the field that merge step has created (field1_1). You can use "Set field value" step, in the "Field name" column put field1 and in the "Replace by value from field" put field1_1. Maybe you need to use a condition before this because it is possible you do not want to to this for all the cases.

    I have attached a similar step but adding "Set field value" step.

    Hope this helps.
    Regards,
    Aitor
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2011
    Posts
    840

    Thumbs up Solved: Multiple Inputs, Single Output

    Quote Originally Posted by augarte View Post
    So I guess you want table 2's field1 to have the same value of the field that merge step has created (field1_1). You can use "Set field value" step, in the "Field name" column put field1 and in the "Replace by value from field" put field1_1. Maybe you need to use a condition before this because it is possible you do not want to to this for all the cases.

    I have attached a similar step but adding "Set field value" step.
    Aitor, thanks a LOT! I finally figured out a way! Couldn't have done it without your idea! I'm attaching the solution I've found. If anyone care to take a look and suggest improvements, I'm on!
    multiple inputs single output.ktr
    I guess I got to explain fairly well the mechanics with notes... Maybe my first help to the community?
    Now, on to test on the problem itself to see if my theorics were good enough! I'll keep you guys informed!

    PS: btw, how do you make that "attached file" field? Mine always get posted as the filename only...

  8. #8
    Join Date
    Sep 2010
    Posts
    135

    Default

    Hi Joao,

    I'm glad you finally could solve it !!! I was thinking about a solution but it is difficult to figure out which is the problem when you don't know the input tables exactly.

    To attach the field, first you have to select "Go advanced" and after this, in the "Attachments" side, select "Manage Attachments" option. From here you are able to attach all the files you want.

    Regards,
    Aitor

  9. #9
    Join Date
    Feb 2011
    Posts
    840

    Default

    yeah, just tested it here, works like a charm! And I guess what happened was that I was using "insert inline" for the attachments...

    -= edit =-
    just realized there were some errors on a couple Select values.
    Attached Files Attached Files
    Last edited by joao.ciocca; 03-02-2011 at 01:52 PM.

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.