topic Re: Table join in QlikView

Table join

upaliwije — Thu, 03 Sep 2015 05:42:47 GMT

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

Re: Table join

jonathandienst — Thu, 03 Sep 2015 06:13:55 GMT

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.

Re: Table join

Digvijay_Singh — Thu, 03 Sep 2015 06:19:25 GMT

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?

Re: Table join

Digvijay_Singh — Thu, 03 Sep 2015 06:49:16 GMT

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.

Re: Table join

kavita25 — Thu, 03 Sep 2015 07:18:30 GMT

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

Re: Table join

upaliwije — Thu, 03 Sep 2015 07:27:12 GMT

That should come under paid 12000 and 4000

Re: Table join

kavita25 — Thu, 03 Sep 2015 07:34:41 GMT

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 ???

Re: Table join

upaliwije — Thu, 03 Sep 2015 08:29:06 GMT

Yes

Re: Table join

kavita25 — Thu, 03 Sep 2015 08:41:00 GMT

See the attached qvw...

hope it helps you!!!

Re: Table join

upaliwije — Thu, 03 Sep 2015 08:51:33 GMT

File not attached. pls attach the same

Re: Table join

upaliwije — Thu, 03 Sep 2015 09:20:21 GMT

Dear Kavita

Pls send the attachment

Re: Table join

kavita25 — Thu, 03 Sep 2015 09:37:09 GMT

look at my previous post...file is attached..

Re: Table join

upaliwije — Thu, 03 Sep 2015 09:46:30 GMT

THERE I DONT FIND THE ATTACHMENT PLS REATTACH

Re: Table join

kavita25 — Thu, 03 Sep 2015 10:06:48 GMT

PFA