Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.