Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: How to create same layout for later merging...

  1. #1

    Default How to create same layout for later merging...

    ...Hi

    Ive got serious problem in Kettle. Ive got 8 results from transformation processes. Now i want to get those 8 results to one table containing layout of specified 8 columns. The problem is that always when i want to merge those 8 columns into this table this error appear:

    "The name of field number 1 is not the same as in the first row received: you're mixing rows with different layout. Field [firm String(255)] does not have the same name as field [surname String(40)]."

    I found on this forum that i need to have same input layout as output table layout.

    The Question is: where can i set in Kettle layout for input results from problem described above ??? I tried so many settings in Kettle but still the same problem.

    Thx a lot

    Paul

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

    Default

    With a select value step you can always rearrange the fields in whichever order you need.

    Ordering of the new fields in PDI is mostly deterministic, so it doesn't randomly choose which field to put where. If you use the same actions in different streams you will get the fields in the same position. Failing that... "select value step".

    As a trick:
    - show outputs/inputs when you right click on a step... it will show you what will be output (in 2.5 some steps like merge join had problems with this feature)
    - preview steps before you hit the problem of mixing rows.

    Regards,
    Sven

  3. #3

    Default

    I see. Thanks but how can i change layout than ? I used Select Value, but it doesnt work :-(

    Quote Originally Posted by sboden View Post
    With a select value step you can always rearrange the fields in whichever order you need.

    Ordering of the new fields in PDI is mostly deterministic, so it doesn't randomly choose which field to put where. If you use the same actions in different streams you will get the fields in the same position. Failing that... "select value step".

    As a trick:
    - show outputs/inputs when you right click on a step... it will show you what will be output (in 2.5 some steps like merge join had problems with this feature)
    - preview steps before you hit the problem of mixing rows.

    Regards,
    Sven

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

    Default

    Unless I'm very wrong, the output of the fields is the order you put them in the select values step.

    Regards,
    Sven

  5. #5

    Default

    I add another column to input columns to be sure that name and count of column is same as output table. But it still show the error...i think the order of table must be same as well....where can i set it pls ?








    BTW: i am getting angry with this tool :-( i thought that work will be easier instead of creating sql procedures, but experience shows opossite

    Quote Originally Posted by sboden View Post
    Unless I'm very wrong, the output of the fields is the order you put them in the select values step.

    Regards,
    Sven

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

    Default

    The column order in the table has priority, you have to make sure your rows have the columns in the right order.

    But that's not even your problem (looking at your original post)... your first problem is that you're mixing rows... you generate rows via different streams and all rows arriving at 1 step have to be the same... names, count, order, types.

    Example attached that reorders columns, remove the select value step and you will get your error.

    Best Regards,
    Sven

    P.S. we're not cramming Kettle down your throat , you have a free choice. Alternatives: Talend (which is also open source but GPL), DataStage, Informatica, ...
    And when you've being writing sql procedures for the last x years, procedures will probably be easier short-term yes.
    Attached Files Attached Files

  7. #7

    Default

    OK...so where can i change order of columns in table ? Thats all what i need now....thx



    Quote Originally Posted by sboden View Post
    The column order in the table has priority, you have to make sure your rows have the columns in the right order.

    But that's not even your problem (looking at your original post)... your first problem is that you're mixing rows... you generate rows via different streams and all rows arriving at 1 step have to be the same... names, count, order, types.

    Example attached that reorders columns, remove the select value step and you will get your error.

    Best Regards,
    Sven

    P.S. we're not cramming Kettle down your throat , you have a free choice. Alternatives: Talend (which is also open source but GPL), DataStage, Informatica, ...
    And when you've being writing sql procedures for the last x years, procedures will probably be easier short-term yes.

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

    Default

    Not.... you have to change your rows to match the table columns.

    Regards,
    Sven

  9. #9

    Default

    Yes i got your point....I changed it but it still doesnt work...Now ive got two input tables with layout A,B,C and C,B,A and output table with layout B,A,C .... And it still doesnt work :-( i think that i need to sort order of columns in input tables so from A,B,C to B,A,C and from C,B,A to B,A,C....i tried this with Select value step but it still doesnt wor so where can i make it ?????

    Is that clear now ????


    Quote Originally Posted by sboden View Post
    Not.... you have to change your rows to match the table columns.

    Regards,
    Sven

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

    Default

    Quote Originally Posted by paveluk View Post
    i think that i need to sort order of columns in input tables so from A,B,C to B,A,C and from C,B,A to B,A,C....i tried this with Select value step but it still doesnt wor so where can i make it ?????
    That's correct... Look at the example I attached before, that will do a reordeing of columns using Select Values step.

    What I would suggest...
    - Make a copy of your transformation
    - Replace the table output by a text output step
    - Reorder the columns in your rows so that they're in the order of your original table, and that it works using text output step.
    - If you get that working replace text output again by table output.

    Regards,
    Sven

  11. #11

    Default

    That is exactly what i trying....I will use real table in case when it will work...i am doing exactly what are you writting...i dont understand why it doesnt work :-(


    Quote Originally Posted by sboden View Post
    That's correct... Look at the example I attached before, that will do a reordeing of columns using Select Values step.

    What I would suggest...
    - Make a copy of your transformation
    - Replace the table output by a text output step
    - Reorder the columns in your rows so that they're in the order of your original table, and that it works using text output step.
    - If you get that working replace text output again by table output.

    Regards,
    Sven

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

    Default

    You're too blinded by your output table to see your problem. Get it to work with text output and it will work with table output.

    Regards,
    Sven

  13. #13

    Default

    You wrote that every column in table has got priority...Where can i set this priority ?

    Thx

    Quote Originally Posted by paveluk View Post
    That is exactly what i trying....I will use real table in case when it will work...i am doing exactly what are you writting...i dont understand why it doesnt work :-(

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

    Default

    The order of the columns in your table has priority over the columns in your rows (using Table output).

    What I meant with that is that the order of the columns you have supply to table output has to be in the exact order of the table. There's not yet a way to specify the order in table output.

    The columns of your row will be binded/bound positionally to the table. If you've got a table like

    A varchar2...
    B integer

    then the fields in your rows should be "A, B" (first A then B)

    Regards,
    Sven

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

    Default

    Loose the table output for now, make it work with text output first. It will save you a lot of headaches

    In text output step you also have to make sure that all of the columns are in the right order.

    Regards,
    Sven

  16. #16

    Default

    I UNDERSTAND...I did everything, but how can i set same order of columns ? Setect value step doesnt help...thx




    Quote Originally Posted by sboden View Post
    Loose the table output for now, make it work with text output first. It will save you a lot of headaches

    In text output step you also have to make sure that all of the columns are in the right order.

    Regards,
    Sven

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

    Default

    I UNDERSTAND...I did everything, but how can i set same order of columns ? Setect value step doesnt help...thx
    1. The prime goal of the "Select Values" step is to select and re-order values. Of-course it does exactly that what you want. The "Select" tab does what you need. (and more)
    2. The "Table output" step doesn't care about the order of the fields. It does matter that the same fields appear in the same order in all input rows.
    3. If you want further help I highly suggest you attach the transformation you're working on.

    Matt

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.