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.

       

      KeyValue
      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_1Value_1
      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!

      Tanya

        • 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!

           

           

           

          Load

              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.