1 Reply Latest reply: Sep 24, 2012 8:40 PM by Kevin Littich RSS

    Convert 1:M to M:M from Sharepoint?

      Hello QlikView Friends!


      I am trying to convert some data that is coming in the following format from a multiple-choice Sharepoint list, and I cannot figure out how to do it.


      Here's how my data is appearing.


      Key AData-1;#Data B;#DataC;#Data-Data;
      Key BData-1;#Data B
      Key CData-1
      Key DDataC;#Data-Data


      And here is how I would like it to appear (so that I can create a metric on how often each value is appearing, regardless of the Key).


      Key AData-1
      Key AData B
      Key ADataC
      Key AData-Data
      Key BData-1
      Key BData B
      Key CData-1
      Key DDataC
      Key DData-Data


      Please advise if you know how to do this.

      Thank you so much!


        • Re: Convert 1:M to M:M from Sharepoint?

          Hi Tanya!


          The simple case of this is accomplished with the built-in Subfield() function in QlikView - nothing complicated required!





              Key as Key_1,

              Subfield( Value, ';' ) as Value_1

          Resident RawData;



          What's happening?  When used in a load statement and without specifying the optional third argument denoting the specific subfield you are after, the Subfield() function will generate one record for each subfield automatically.