3 Replies Latest reply: Nov 12, 2014 7:42 PM by haziq@myigen.com _ RSS

    How to combine data into one field

    haziq@myigen.com _

      Hello datawatch specialist, in Datawatch Modeler, how to combine the entire artist name inside one field?


      For example, in one XML file got one main artist and three secondary (featuring) with another artist. And the output for the artist_dj_name  must be main artist and following with another three secondary artist separate by comma (,). Plus, it is possible to make the artist_id not redundant?


      Can refer the sample xml and table below.


      <----Start for XML artist_1---->

      <Artists>

           <ArtistText>artist_1</ArtistText>

       

                <Artist Type="Main">

       

       

              <FullName>artist_1</FullName>

       

           </Artist>

       

       

       

              <Artist Type="Secondary">

       

       

                <FullName></FullName>

       

           </Artist>

       

      </Artists>

      -------------------------------------------------------------

      <Artists>

       

           <ArtistText>artist_1</ArtistText>

       

       

           <Artist Type="Main">

       

       

                 <FullName>artist_1</FullName>

       

           </Artist>

       

           <Artist Type="Secondary">

       

       

                <FullName>artist_a</FullName>

       

           </Artist>

      </Artists>

      -------------------------------------------------------------

      <Artists>

       

           <ArtistText>artist_1</ArtistText>

       

                <Artist Type="Main">

       

       

                   <FullName>artist_1</FullName>

       

           </Artist>

       

       

       

              <Artist Type="Secondary">

       

       

                   <FullName>artist_b</FullName>

       

           </Artist>

       

      </Artists>

      <----End for XML artist_1---->


      <----Start for XML artist_2---->

      <Artists>

       

           <ArtistText>artist_2</ArtistText>

       

       

           <Artist Type="Main">

       

       

                <FullName>artist_2</FullName>

       

           </Artist>

       

           <Artist Type="Secondary">

       

       

                <FullName>artist_c</FullName>

       

           </Artist>

      </Artists>

      -------------------------------------------------------------

      <Artists>

       

           <ArtistText>artist_2</ArtistText>

       

                <Artist Type="Main">

       

       

                <FullName>artist_2</FullName>

       

           </Artist>

       

       

       

              <Artist Type="Secondary">

       

       

                <FullName>artist_d</FullName>

       

           </Artist>

       

      </Artists>

      <----End for XML artist_2---->

       


      Current Result

      xmlartist_idartist_dj_name
      1artist_1artist_1
      1artist_1artist_1, artist_a
      1artist_1artist_1, artist_b
      2artist_2artist_2, artist_c
      2artist_2artist_2, artist_d

       

      Expect Result

      xmlartist_idartist_dj_name
      1artist_1artist_1, artist_a, artist_b,
      2artist_2artist_2, artist_c, artist_d


      Hope that you guys can help me for this issue.


      Thank You.

        • Re: How to combine data into one field
          rlandeo _

          Hi,

           

          You can use the concatenate function to combine several fields into one. You would have to create a formula field [artist_dj_name] of type character with the following formula:

          Concatenate_XL([artist_1[,", ",[artist_2],", ",[artist_3])

           

          I have to think about how to combine rows.

            • Re: How to combine data into one field
              Olly Bond

              Hello Haziq, hello Raul,

               

              This is a lovely example of the "missing detail" problem. Generally, parsing reports is simple - there's a single line per key transaction, and we can use appends and footers to grab the other information we need. That's fine, as long as there's only ever one instance of each footer or append for each detail. But sometimes, we have heirarchies of information, so many parents have many children, and some have none.

               

              In this case, you always have (I assume) Artist Main. You sometimes have 0, 1, or many Artist Secondary entries. A couple of practical limits: Do you know for certain that you'll never have more than 99 secondary artists on one entry? Depending on the other XML tags you're faced with, from the Artist Main entry, are there ever more than 254 text lines until the end of the Artist Secondary entries?

               

              If you can let me have a sample XML file, I'll happily send you data in Excel.

               

              Best wishes,

               

              Olly