Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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