Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: joining fileds (Not tables)

  1. #1
    Join Date
    Sep 2008
    Posts
    26

    Default joining fileds (Not tables)

    Hi,

    I have been stumped with this problem for a day now and really need some help.

    I am trying to normalise the following row in a table and create multiple rows

    Area; numbers; cost
    sydney; 612,613,614,615; 0.1,0.2,0.2,0.3

    to
    Area; numbers; cost
    sydney; 612; 0.1
    sydney; 613; 0.2
    sydney; 614; 0.2
    sydney; 615; 0.3

    I use two Split Rows of the text file and I can get the numbers and the cost into columns, but I have no idea how to rejoin the two split columns of numbers and cost back into one table.

    Any help would be appreciated here.

    Thanks
    Des

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

    Default

    With a "row normaliser" step.

    normalize.ktr

  3. #3
    Join Date
    Sep 2008
    Posts
    26

    Default

    Matt

    Thanks for the fast reply. I tried your suggestion and it works for fixed length row data. Bugger is I have rows that can be any length. This forces me to use the split fields into rows component as opposed to the split fields component.

    e.g.

    Area; numbers; cost
    sydney; 612,613,614,615; 0.1,0.2,0.2,0.3
    USA; 1121,1123,1214,1615,1345,1356; 0.1,0.2,0.2,0.3,0.4,0.1
    UK: 441,449; 0.22,0.34

    I'm ok with splitting the fields. It's just the joining of them that is screwing me up. I just need to reassemble them similar to a SQL "SELECT fieldsA, fieldB, fieldC" statement, but with no FROM part. The result of the above needs to be as follows:

    Area; numbers; cost
    sydney; 612; 0.1
    sydney; 613; 0.2
    sydney; 614; 0.2
    sydney; 615; 0.3
    USA; 1121; 0.1
    USA; 1123; 0.2
    USA; 1214; 0.2
    USA; 1615; 0.3
    USA; 1345; 0.4
    USA; 1356; 0.1
    UK; 441; 0.22
    UK; 449; 0.34

    Really would appreciate if you could give me some hints as where to look or maybe some insight to how to rejoin fields.

    Thanks
    Des

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

    Default

    Personally I would throw a stapler at the people making you use such a file

    Only way to process those (as I see now), would be a javascript snippet parsing your numbers and cost field and generating new rows with the parsed values.

    Regards,
    Sveb

  5. #5
    Join Date
    Jan 2008
    Posts
    9

    Default

    Hi Des,
    I think the europeans may be sleeping right now. I can't say for sure what the right way is, but I'd give a javascript step a go that adds more rows to the result set. See '{pentaho home}/samples/transformations/JavaScript - create new rows.ktr' for the adding rows.

    Cheers
    Brett

  6. #6

    Default

    you do know its gone 8:30 don't you?
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

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

    Default

    I'm always into these challenges. I always try to find a solution WITHOUT JavaScript
    These days I usually find it.

    normalize.ktr

  8. #8
    Join Date
    Sep 2008
    Posts
    26

    Smile

    Well, I am impressed. I have been stumped for two days on this. Admittedly I am still a bit new at this, but I continue to be impressed with what you guys come up with.

    I tried it with different length codes and costs and it worked fine. Now I have to figure out what the steps do and enhance my knowledge. ;-)

    Thank you so much and hopefully I can add some help sometime.

    Thanks
    Des

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.