topic Re: Data Model in QlikView

Data Model

upaliwije — Thu, 20 Feb 2014 09:31:34 GMT

I have attached two excel files

Premium

Claims

From the above two files I want your help to make data model to obtain following reports (pivot Table)

Dimension Facts

Policy No Premium

No of Claims

Paid Amount

When the above information is extracted loss_date(claims) should be between From Date and To_date of the policy.

I shall be much thankful if some one help me to build a data model with my attached files

Re: Data Model

MK_QSL — Thu, 20 Feb 2014 09:43:17 GMT

Can you upload your expected result also in Excel File?

Re: Data Model

Gysbert_Wassenaar — Thu, 20 Feb 2014 09:47:30 GMT

See attached qvw.

Re: Data Model

jvitantonio — Thu, 20 Feb 2014 09:48:08 GMT

Hi Upali,

Please check out the attached app. I think this is what you are looking for.

thanks

Re: Data Model

er_mohit — Thu, 20 Feb 2014 09:53:18 GMT

See the attached file

Re: Data Model

upaliwije — Thu, 20 Feb 2014 09:55:17 GMT

Hi Manish

Thanks

This is what I am expeceting

Policy No	Premium	No of Claims	Claim Paid
1	25,000	3	210,000
2	50,000	1	25,000
3	30,000	2	95,000
4	10,000	2	140,000
5	40,000	2	150,000
6	45,000	0	-
7	35,000	0	-
8	60,000	0	-
9	6,000	0	-
10	12,000	0	-
Note
1	C4	1/8/2015	1/18/2015	40,000	Loss date of these two claims are not within policy Period
5	C10	11/10/2015	11/20/2015	40,000

Re: Data Model

upaliwije — Thu, 20 Feb 2014 10:09:56 GMT

Hi Sahrma

Thanks for your reply. Can you list in the pivot table other policies where there no claims as well (all ten policies)

Re: Data Model

upaliwije — Thu, 20 Feb 2014 10:21:02 GMT

1	C4	1/8/2015	1/18/2015	40,000	Loss date of these two claims are not within policy Period
5	C10	11/10/2015	11/20/2015	40,000

The above two claims should not appear in the pivot since loss date not within policy period

Re: Re: Data Model

Thu, 20 Feb 2014 10:22:38 GMT

hi upali

PFA

hope this helps

Re: Data Model

er_mohit — Thu, 20 Feb 2014 10:26:43 GMT

@See the attached file again

Re: Data Model

upaliwije — Thu, 20 Feb 2014 10:27:31 GMT

Thanks

But Two Claims should be excluded C4 and C10 They are not within period

Re: Data Model

upaliwije — Thu, 20 Feb 2014 10:32:34 GMT

Thanks

These two claim should be excluded. Pls help

1	C4	1/8/2015	1/18/2015	40,000	Loss date of these two claims are not within policy Period
5	C10	11/10/2015	11/20/2015	40,000

The above two claims should not appear in the pivot since loss date not within policy period

Data Model

Re: Data Model

jvitantonio — Thu, 20 Feb 2014 10:35:21 GMT

Upali, I think the problem is in your data source. In the premium file for Policy No = 1, you have a

Date From 01/01/2014 and a Date To 31/12/2013. Date To should be earlier thank Date From.

Re: Data Model

Thu, 20 Feb 2014 11:15:50 GMT

great Observation

Re: Data Model

upaliwije — Thu, 20 Feb 2014 11:21:00 GMT

It is true there is a mistake. Even if the the mistake is corrected my issue not corrected

Re: Re: Data Model

jvitantonio — Thu, 20 Feb 2014 12:02:46 GMT

Hi Upali,

Check out the attached file.

Re: Data Model

MK_QSL — Thu, 20 Feb 2014 12:17:03 GMT

I am almost there.... But the result only showing when you select the Policy Number.

Can someone correct it. would be thankful.

Re: Data Model

upaliwije — Fri, 21 Feb 2014 03:18:32 GMT

Thanks all for your time taken