Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Concatenating Data from Multiple Rows

  1. #1

    Default Concatenating Data from Multiple Rows

    I am trying to work out whether is it possible to concatenate fields from multiple rows into a single field for output.

    Essentially, I have a table which holds text data entered into a program.
    The fields are ID Number, Line Number & Text.
    What I need to be able to do is move all the text data for a single ID number into a single text field.

    For example,
    What I have

    2, 1, First Line of Text
    2, 2, Second Line of Text
    2, 3, Third Line of Text

    What I need

    2, First Line of Text Second Line Of Text Third Line of Text

    Is this possible in Kettle??
    If so, how?

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    It's easily done with the Group By step from the Statistics category.
    Use ID as key and select a suitable concatenation function.
    Make sure the input rows are sorted by (ID, Line).
    So long, and thanks for all the fish.

  3. #3

    Default

    Thankyou.
    That worked exactly how I wanted it to.

    Although now I need to modify the results slightly.

    In some cases my data is more like this:

    2, 1, Line of Text
    2, 4, Line of Text
    2, 5, Line of Text
    2, 7, Line of Text

    What I need to do is 'fill-in' the missing line numbers with blank text.
    So I would have:

    2, 1, Line of Text
    2, 2,
    2, 3,
    2, 4, Line of Text
    2, 5, Line of Text
    etc.

    I realise there probably isn't a built in way to do this.
    But doesn't anyone know how it could be done?

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

    Default

    If you read the file using Text File Input, there's a check box to fill in empty records.
    In the Fields tab, look for "Repeat".
    -- Mick --

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Hmm, here's one more way to do it.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6

    Default

    Thankyou again.

    I just have one more thing I need to do with this data.
    That is put a line break at the end of every line.
    I presume that I could simply use a Java Class to add "/n" to the end of every row?

    But I thought I'd see if there was an easier/better way before I spend the time doing it tomorrow.

    Thanks

  7. #7
    Join Date
    Jul 2017
    Posts
    4

    Default cant get group by to work

    Quote Originally Posted by marabu View Post
    It's easily done with the Group By step from the Statistics category.
    Use ID as key and select a suitable concatenation function.
    Make sure the input rows are sorted by (ID, Line).
    This would be a very valuable tool for me but I simply cant get any of the group by functions to work
    I have data exactly like the example here
    a simple concatenation is all I need

    Id line Text
    1 1 Sam
    1 2 Tom
    1 3 Max

    I want a single line like
    1 Sam Tom Max

    Any help you can offer will be great
    Last edited by phunhog; 07-31-2017 at 12:44 PM.

  8. #8
    Join Date
    Feb 2016
    Posts
    22

    Default

    This is an example of how you can achieve what you need to do using group by:
    Group_by_Example.zip

  9. #9
    Join Date
    Jul 2017
    Posts
    4

    Default A little more work and I got it

    Quote Originally Posted by phunhog View Post
    This would be a very valuable tool for me but I simply cant get any of the group by functions to work
    I have data exactly like the example here
    a simple concatenation is all I need

    Id line Text
    1 1 Sam
    1 2 Tom
    1 3 Max

    I want a single line like
    1 Sam Tom Max

    Any help you can offer will be great
    1 day later I returned and with a little more work I've succeeded.... Thanks Pentaho forums

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.