topic How do I avoid loops in my design please? in QlikView

How do I avoid loops in my design please?

Thu, 22 Sep 2011 15:17:06 GMT

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?

jonathandienst — Thu, 22 Sep 2011 18:27:04 GMT

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

How do I avoid loops in my design please?

sukydhak — Thu, 22 Sep 2011 19:07:03 GMT

You have two options

1 Concatenate all the data into one table

or

2. Link Tables.

Suky