Extracting multiple data from one field

Tue, 05 Mar 2013 14:27:45 GMT

Hello,

I am having a problem with one field that is being pulled from Sharepoint.

It basically is a lookup field that can contain numerous data:

Some background:

- This is a change management report tool which lists the number of change requests per application

- A group of Change Requests are piled up into a Release

The problem arises that when in Qlikview, looking at what CRs are included in a Release the field looks like:

Table A

Release ID	Rel Name	Associated CRs
1	Release A	CR1
2	Release B	CR2
3	Release C	CR1; CR3; CR4; CR5

Table B

Change ID	CR Name
1	CR1
2	CR2
3	CR3
4	CR4
5	CR5

In order to accurately link the CR in the Release table (A), to the CR objects (table B) I need to somehow extract the objects out individually.

I have tried using the

SubField([Associated CRs], ';') as NewField function but it only brings back the first CR from the left while leaving all the other ones (CR3,4 and 5 in example above)

Is there a way of returning the Release table to look like this?

Release ID	Rel Name	Associated CRs	NewField
1	Release A	CR1	CR1
2	Release B	CR2	CR1
3	Release C	CR1; CR3; CR4; CR5	CR1
3	Release C	CR1; CR3; CR4; CR5	CR3
3	Release C	CR1; CR3; CR4; CR5	CR4
3	Release C	CR1; CR3; CR4; CR5	CR5

Re: Extracting multiple data from one field

Gysbert_Wassenaar — Tue, 05 Mar 2013 15:02:47 GMT

You can use the subfield function:

T1:

Load

[Release ID],

[Rel Name],

[Associated CRs],

trim(subfield([Associated CRs],';')) as NewField

from ...somewhere...;

See attached example

Re: Extracting multiple data from one field

Tue, 05 Mar 2013 15:16:27 GMT

thanks! that worked

topic Re: Extracting multiple data from one field in QlikView

Extracting multiple data from one field

Re: Extracting multiple data from one field

Re: Extracting multiple data from one field