topic Re: LINK 4 FACT TABLE in QlikView

LINK 4 FACT TABLE

upaliwije — Sat, 13 Sep 2014 16:23:22 GMT

I have following 4 tables

PREMIUM:

LOAD

CLA_CODE,

PRD_CODE,

POLICY_NO,

POL_PERIOD_FROM,

POL_PERIOD_TO,

BCOD,

TRN_DATE AS RISK_DATE,

PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd)

RI_PREMIUM:

POLICY_NO,

TRA_BRANCH AS BCOD,

TRA_POLICY_NO AS POLICY_NO,

TRN_DATE AS RISK_DATE,

PREMIUM AS RI_PREMIUM,

COMMISSION AS RI_COMM,

TRA_VALID_FR_DT AS POL_PERIOD_FROM,

TRA_VALID_TO_DT AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

(qvd);

CLAIM_PAID:

CLAIM_NO

POLICY_NO,

BCOD,

PRD_CODE,

PERIOD_FROM AS POL_PERIOD_FROM,

PERIOD_TO AS POL_PERIOD_TO ,

CLAIM_PAID_AMOUNT

FROM

[..\..\QVD\FACT_Table\Claims.qvd]

(qVd)

RECOVERY:

POLICYNO as POLICY_NO,

CLAIMNO as CLAIM_NO,

PAYAMT,

RECAMT AS RECV_AMOUNT,

PRD AS PRD_CODE,

BCOD

FROM

RI_RECOVERY.QVD

(qvd)

these four table relates data for insurance

Premium= data for policy premium figures

RI_Premium= Reinsurance data for policy premium

Claims_paid= Insurance claims paid

Recovery =Recovery for claim paid

I want to relate above 4 tables on the following basis

RI_Premium against Premium

Recovery against Claims_paid

Finally I want to create a report as follows

Bcod, Policy No, Premium, RI_premium,CLAIM_PAID_AMOUNT,RECV_AMOUNT RELATING TO POLICY PERIOD

pLS HELP ME WITH DATA MODEL FOR THIS

Re: LINK 4 FACT TABLE

don_qlikview — Sat, 13 Sep 2014 17:19:27 GMT

See if the attached sample qvw works for you.

Re: LINK 4 FACT TABLE

Sat, 13 Sep 2014 19:27:31 GMT

try this

PREMIUM:

LOAD

POLICY_NO as key1,

CLA_CODE,

PRD_CODE,

POLICY_NO,

POL_PERIOD_FROM,

POL_PERIOD_TO,

BCOD,

TRN_DATE AS RISK_DATE,

PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd)

RI_PREMIUM:

POLICY_NO as key1,

// POLICY_NO,

TRA_BRANCH AS BCOD,

TRA_POLICY_NO AS POLICY_NO,

TRN_DATE AS RISK_DATE,

PREMIUM AS RI_PREMIUM,

COMMISSION AS RI_COMM,

TRA_VALID_FR_DT AS POL_PERIOD_FROM,

TRA_VALID_TO_DT AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

(qvd);

CLAIM_PAID:

POLICY_NO &'|'& CLAIM_NO as key2,

CLAIM_NO,

POLICY_NO ,

BCOD,

PRD_CODE,

PERIOD_FROM AS POL_PERIOD_FROM,

PERIOD_TO AS POL_PERIOD_TO ,

CLAIM_PAID_AMOUNT

FROM

[..\..\QVD\FACT_Table\Claims.qvd]

(qVd)

RECOVERY:

POLICYNO &'|'& CLAIMNO as key2,

POLICYNO ,

CLAIMNO ,

PAYAMT,

RECAMT AS RECV_AMOUNT,

PRD AS PRD_CODE,

BCOD

FROM

RI_RECOVERY.QVD

(qvd)