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.
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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
