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