Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to split long string into multiple rows in same column

  1. #1

    Post Split long string into multiple rows in same column

    I need to split long string like the below into multiple lines based on the length and delimiter(which should remain) and if the lenth number falls between delimiter then need to split the last delimiter

    Input:
    LineNum Data
    1 20001+20002+20003+20005+20019+20035+20009+20011+20015+20006+20020+20047+20048+20050+20049+204044+22407+20052+20057+20058+20059+20063+20065+20067+20068+20070+20072+20073+20075+20076+20078+20081+20084+20085+20086+20140+21954+206171+206170+206175+20093+206168+206177+206172+20098+206167+20107+20053+20054+20056+20108+20109+20110+20112+20115+20117+20119+20124+20126+20131+20132+20136+20141+20344+20345+20346+20348+20349+20355+20356.A

    I accomplished the above using "Group By - Concatenate separated by +"

    Desired Output:

    LineNum Data
    1 20001+20002+20003+20005+20019+20035+20009+20011+20015+20006+20020+20047+20048+20050+20049+204044+22407
    2 20052+20057+20058+20059+20063+20065+20067+20068+20070+20072+20073+20075+20076+20078+20081+20084+20085
    3 20086+20140+21954+206171+206170+206175+20093+206168+206177+206172+20098+206167+20107+20053+20054
    4 20056+20108+20109+20110+20112+20115+20117+20119+20124+20126+20131+20132+20136+20141+20344+20345
    5 20346+20348+20349+20355+20356.A

    Length = 100 (again depends on the delimiter position)

    I was thinking of doing this in Javascript but, I don't know how to accomplish this, please give some guidance.
    Last edited by saianupkumarp; 12-09-2016 at 05:53 AM. Reason: Question title

  2. #2
    Join Date
    Aug 2011
    Posts
    360

    Default

    So before your group by you had one row per value?

  3. #3

    Default

    Quote Originally Posted by Mathias.CH View Post
    So before your group by you had one row per value?
    Yes Mathias.

    However, I have written a JS to do that and then used split fields to rows with a delimiter, below is the code which might be useful to others,

    var startNum = 0, min= 1950, startPosition = 0, endResult = [];


    if (conCatLen > '1950') {
    while (startPosition != -1) {
    startPosition = conmetaVal.indexOf('+', startNum + min);
    endResult.push(conmetaVal.substring(startNum , startPosition == -1? conmetaVal.length : startPosition ));
    startNum = startPosition +1;
    }
    }else{
    var endResult= conmetaVal
    }

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

    Default

    Line-wrapping with Apache Commons
    Attached Files Attached Files
    So long, and thanks for all the fish.

  5. #5

    Default

    Quote Originally Posted by marabu View Post
    Line-wrapping with Apache Commons
    Wow! this is something new that i learnt today. Thanks Marabu.

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.