2 Replies Latest reply: Mar 5, 2013 10:16 AM by Marek Les RSS

    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