Table join

[upaliwije]

Hi

I have 3 following table and want to join them to obtain requried output report

Table 1
POLICY_NO	POLICY_PERIOD	PREMIUM
1	NOV-10-NOV-11	4000
2	DEC-12-DEC-13	5000
3	JAN-14-JAN-15	2000

Table 2
POLICY_NO	POLICY_PERIOD	PAID
1	NOV-10-NOV-11	30000
2	DEC-12-DEC-13	25000
1	NOV-10-NOV-11	12000
2	DEC-12-DEC-13	4000

Table 3
POLICY_NO	POLICY_PERIOD	TYPE	RATE	AMOUNT
1	NOV-10-NOV-11	BASIC	2000
1	NOV-10-NOV-11	NCB	10	-250
1	NOV-10-NOV-11	LOADING		400
2	DEC-12-DEC-13	BASIC	2500
2	DEC-12-DEC-13	NCB	20	-400
2	DEC-12-DEC-13	LOADING	500
3	JAN-14-JAN-15	BASIC	1500

OUTPUT REPORT
POLICY_NO	POLICY_PERIOD	PREMIUM	PAID	BASIC	NCB	RATE	LOADING
1	NOV-10-NOV-11	4000	30000	2000	-250	10	400
12000
2	DEC-12-DEC-13	5000	25000	2500	-400	20	500
4000
3	JAN-14-JAN-15	2000	0	1500

Appreciate very much your  help with a sample report for the above

[jonathandienst]

The Result table contains the content after running this script:

Result:

LOAD * Inline

[

POLICY_NO POLICY_PERIOD PREMIUM

1 NOV-10-NOV-11 4000

2 DEC-12-DEC-13 5000

3 JAN-14-JAN-15 2000

] (delimiter is '\t');

Left Join (Result)

LOAD POLICY_NO, PAID

Inline

[

POLICY_NO POLICY_PERIOD PAID

1 NOV-10-NOV-11 30000

2 DEC-12-DEC-13 25000

] (delimiter is '\t');

T3: 

LOAD * Inline

[

POLICY_NO POLICY_PERIOD TYPE RATE AMOUNT

1 NOV-10-NOV-11 BASIC 2000

1 NOV-10-NOV-11 NCB 10 -250

1 NOV-10-NOV-11 LOADING 400

2 DEC-12-DEC-13 BASIC 2500

2 DEC-12-DEC-13 NCB 20 -400

2 DEC-12-DEC-13 LOADING 500

3 JAN-14-JAN-15 BASIC 1500

] (delimiter is '\t');

Left Join (Result)

LOAD POLICY_NO,

  RATE As BASIC

Resident T3

Where TYPE = 'BASIC';

Left Join (Result)

LOAD POLICY_NO,

  RATE As RATE,

  AMOUNT AS NCB

Resident T3

Where TYPE = 'NCB';

Left Join (Result)

LOAD POLICY_NO,

  AMOUNT AS LOADING

Resident T3

Where TYPE = 'LOADING';

DROP Table T3;

You have duplicate lines in Table2 with different values. I am not sure how you want to handle these as the data is not in the final report, so I only loaded the first instance of each policy for table 2.

[Digvijay_Singh]

I am finding your structure very strange, can you answer below questions to understand requirements better -

1. AMOUNT heading is lost in final report but its values are used somewhere in final report, not sure how?

2. TYPE column values of third table it seems are required as columns in final report But RATE is still there with few values, other values distributed for new columns, how?

3. 12000,4000 PAID values are shown in final report below POLICY_NO, why?

[Digvijay_Singh]

I think you have used RATE as LOADING in the last join to reflect your shown result. But in the requirement in 3rd table, 400 value is showing under Amount, might be a typo.

[kavita25]

PFA..

I have a doubt for 12000 and 4000 values under policy no..where to reflect this values under policy no?? and why ??

Rest of the part is completed...

Regards,

Kavita

[upaliwije]

That should come under paid  12000 and 4000

[kavita25]

It means for NOV-10-NOV-11 it should be 30000+12000 i.e 42000  and for DEC-12-DEC-13 4000+25000 i.e 29000 under PAID  Right ???

[upaliwije]

Yes

[kavita25]

See the attached qvw...

hope it helps you!!!

[upaliwije]

File not attached. pls attach the same