Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Extracting multiple data from one field

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 IDRel NameAssociated CRs
1Release ACR1
2Release BCR2
3Release CCR1; CR3; CR4; CR5

Table B

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

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 IDRel NameAssociated CRsNewField
1Release ACR1CR1
2Release BCR2CR1
3Release CCR1; CR3; CR4; CR5CR1
3Release CCR1; CR3; CR4; CR5CR3
3Release CCR1; CR3; CR4; CR5CR4
3Release CCR1; CR3; CR4; CR5CR5
1 Solution

Accepted Solutions

Re: Extracting multiple data from one field

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


talk is cheap, supply exceeds demand
2 Replies

Re: Extracting multiple data from one field

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


talk is cheap, supply exceeds demand
Not applicable

Re: Extracting multiple data from one field

thanks! that worked

Community Browser