2 Replies Latest reply: Sep 22, 2011 3:07 PM by Suky Dhak RSS

    How do I avoid loops in my design please?

    Graham Miller

      I have an existing database I need to pull into Qlikview.  I have the following structure (simplified - detailed table viewer export attached):

       

      Consumers

      Consumer_ID

      Policies

      ConsumerID

      PolicyWarpolrev (ID)

      PolicyClaims

      Claim_Number

      PolicyWarpolrev (ID)

      Non-PolicyClaims (GHSClaim)

      Claim_Number

      Consumer_ID

      Claim Actions

      Claim_Number

       

      This structure creates a loop that Qlikview doesn't like - Claims are linked to Consumers both directly and via Policies.  I need to be able to report on counts of claim actions for policy and non-policy claims together, so I can't just create completely separarate Policyclaimnumber and Nonpolicyclaimnumber structures.  How can I get around this problem?  Thanks for any help you can give.

        • Re: How do I avoid loops in my design please?
          Jonathan Dienst

          Hi

           

          I would consolidate the consumer claims and policy linked claims into a single table. There would three fields in the table:

          Consumer_ID, PolicyWarpolrev (ID) and Claim_Number.

           

          For policy linked claims, all three fields would be populated, for direct consumer linked claims, the PolicyWarpolrev field would be null.

           

          I don't know the structure of your input data sources, so I can only give the skeleton of a load script which you will have to adapt to your data. It would look something like:

           

          DataTable:
          LOAD consumer_id,
               claim_number
               ... other fields ...
          FROM direct claims data source;
           
          Concatenate (DataTable)
          LOAD consumer_id,
               policy_warpolrev
                ... other fields ...
          FROM policy data source;
          
          Join (DataTable)
          LOAD policy_warpolrev,
               claim_number
                ... other fields ...
          FROM policy claims data source;
           
          Join (DataTable)
          LOAD consumer_id,
               ... consumer data fields ...
          FROM consumer masterdata source;
          
           
          

           

          Load the rest of your data normally. The last join may not be necessary, but may improve performance if you have a large data set.

           

          Hope that helps

          Jonathan