JOINING TABLE

[upaliwije]

Hi friends

I have two following tables. Table one contain data relating claims paid in respect of insurance polices. Table 2 has Insurance premium collected on policies for which claims have been paid (given in table one)

TABLE 1
RISK	POLICY_NO	POLICY_PERIOD	CLAIM_NO	PAID_AMOUNT
18-9714	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000041	4,000
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000060	4,250
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000059	11,100
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000092	9,200
	AB00101A000656	Sep-10 - Sep-11		28,550
	AB00101A000656	Total		28,550
19-6047	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000022	10,800
	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000045	4,000
	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000055	6,000
	AB00081A000495	Nov-08 - Nov-09		20,800
	AB00081A000495	Nov-09 - Nov-10	CLAB1A10000037	37,400
	AB00081A000495	Nov-09 - Nov-10		37,400
	AB00081A000495	Nov-10 - Nov-11	CLAB1A11000080	7,450
	AB00081A000495	Nov-10 - Nov-11		7,450
	AB00081A000495	Nov-11 - Nov-12	CLAB1A12000021	20,000
	AB00081A000495	Nov-11 - Nov-12		20,000
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000010	6,000
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000056	17,500
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000142	15,000
	AB00081A000495	Nov-12 - Nov-13		38,500
	AB00081A000495	Nov-13 - Nov-14	CLAB001A14000128	13,950
	AB00081A000495	Nov-13 - Nov-14		13,950
	AB00081A000495	Nov-14 - Nov-15	CLAB001A15000013	7,450
	AB00081A000495	Nov-14 - Nov-15		7,450
	AB00081A000495	Total		145,550
TABLE 2
RISK	POLICY_NO	POLICY_PERIOD	PREMIUM
18-9714	AB00101A000656	Sep-10 - Sep-11	24,000
19-6047	AB00081A000495	Nov-08 - Nov-09	12,000
	AB00081A000495	Nov-09 - Nov-10	13,500
	AB00081A000495	Nov-11 - Nov-12	20,000
	AB00081A000495	Nov-12 - Nov-13	30,000
	AB00081A000495	Nov-14 - Nov-15	21,000

I want to join these tow tables to obtain the report as follows.

RISK	POLICY_NO	POLICY_PERIOD	CLAIM_NO	PAID_AMOUNT	Premium
18-9714	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000041	4,000	24000
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000060	4,250
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000059	11,100
	AB00101A000656	Sep-10 - Sep-11	CLAB1A11000092	9,200
	AB00101A000656	Sep-10 - Sep-11		28,550
	AB00101A000656	Total		28,550
19-6047	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000022	10,800	12000
	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000045	4,000
	AB00081A000495	Nov-08 - Nov-09	CLAB1A09000055	6,000
	AB00081A000495	Nov-08 - Nov-09		20,800
	AB00081A000495	Nov-09 - Nov-10	CLAB1A10000037	37,400	13500
	AB00081A000495	Nov-09 - Nov-10		37,400
	AB00081A000495	Nov-10 - Nov-11	CLAB1A11000080	7,450
	AB00081A000495	Nov-10 - Nov-11		7,450
	AB00081A000495	Nov-11 - Nov-12	CLAB1A12000021	20,000	20000
	AB00081A000495	Nov-11 - Nov-12		20,000
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000010	6,000	30000
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000056	17,500
	AB00081A000495	Nov-12 - Nov-13	CLAB1A13000142	15,000
	AB00081A000495	Nov-12 - Nov-13		38,500
	AB00081A000495	Nov-13 - Nov-14	CLAB001A14000128	13,950
	AB00081A000495	Nov-13 - Nov-14		13,950
	AB00081A000495	Nov-14 - Nov-15	CLAB001A15000013	7,450	21000
	AB00081A000495	Nov-14 - Nov-15		7,450
	AB00081A000495	Total		145,550

Please note that premium should be shown in one row although There are several rows for claim payment. Pls advise me which type of join a I should have in my data model in order to achieve above output

[swuehl]

Have you considered just concatenating the two tables using CONCATENATE LOAD prefix on the second table?

This will not reproduce the table on the bottom of your post, but might fulfill your analysis requirements as well (e.g. filtering on RISK, POLICY_NO or POLICY_PERIOD, and aggregating your PAID_AMOUNT and Premium correctly.

[upaliwije]

Thanks

Sorry I have made a mistake . The Risk field is not available in my second table. Therefore if I do concatenate I can not filter data on Risk field that is my main requirement. Pls advise

[swuehl · Accepted solution]

You can maybe use a MAPPING table created from the first table:

TABLE1:

LOAD

     RISK,

     POLICY_NO,

     POLICY_PERIOD,

     CLAIM_NO,

     PAID_AMOUNT

FROM Table1Source;

RISKMAP:

MAPPING LOAD POLICY_NO, RISK RESIDENT TABLE1;

CONCATENATE (TABLE1)

LOAD ApplyMap('RISKMAP', POLICY_NO,'no mapping found') as RISK,

         POLICY_NO,

         POLICY_PERIOD,

        PREMIUM

FROM Table2Source;

Or could a POLICY_NO have more than 1 RISK?

[Anonymous]

Hi,

try this if that can help you.

Regards