1 Reply Latest reply on Jan 11, 2019 10:03 AM by Joshua Milligan

    how to merge splitted column data

    sree y

      Hi All,

      i have comma separated values in id and name column .

        

      idname
      1a
      1,2a,b
      1,2,3a,b,c
      ,1,1,a,a
      2,3,6b,c,f
      2,4,5,3,1b,d,e,c,a

       

       

      How to get required output as below.

       

      Id name

      1 a

      2 b

      3 c

      4 d

      5 e

      6 f

       

      Thanks

      sreeni

        • 1. Re: how to merge splitted column data
          Joshua Milligan

          Sree,

           

          Here is how I would approach it:

           

          I would split both name and id fields using a Custom Split

           

          You can see that that gives you Split 1, Split 2, etc... that matches the position in the string:

           

          Go ahead and remove the id and name fields after you've split them as you won't need them anymore.

           

          Then, you can do a coordinated pivot on each of the split fields:

           

           

          You're really close, but you'll also want to filter out the NULL/blank values and probably use an aggregate to remove any duplicates:

           

          I've attached a packaged flow files (.tlfx) so you can dig into the details!

           

          Hope that helps!

          Joshua