Convert 1:M to M:M from Sharepoint?

[#1]

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	Value
Key A	Data-1;#Data B;#DataC;#Data-Data;
Key B	Data-1;#Data B
Key C	Data-1
Key D	DataC;#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_1	Value_1
Key A	Data-1
Key A	Data B
Key A	DataC
Key A	Data-Data
Key B	Data-1
Key B	Data B
Key C	Data-1
Key D	DataC
Key D	Data-Data

Please advise if you know how to do this.

Thank you so much!

Tanya

[#2]

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.