Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

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

Tags (1)
1 Reply
Not applicable

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.

Community Browser