topic Re: Calculation in load script using master calendar dates in QlikView

Calculation in load script using master calendar dates

Anonymous — Tue, 06 Sep 2016 13:26:12 GMT

Dear all,

I have need some expert advise for my problem

I have created a master calendar in my project and also I have table which has 2 date fields.

Sample 2 rows from my data:

Start Date	End Date	Premium	ContractNo
01/01/2016	31/12/2016	1000	1
01/02/2016	01/02/2017	500	2

I have a monthenddate field in my master calendar which is basically last day of the each month.

My formula is : (monthenddate - Start Date) / (End Date - Start Date) * Premium

I would like to run this formula in load script for each contract for the each month end date and my I should reach a data set which has

12 different rows for each contract after running this formula like

Policy no, Monthenddate, Function Result

1 31/01/2016 10

1 28/02/2016 15

1 31/03/2016 20

.......

1 31/12/2016 100

Your helps will be appreciated.

Many thanks,

Re: Calculation in load script using master calendar dates

kaushiknsolanki — Tue, 06 Sep 2016 17:38:38 GMT

Hi,

Have a look at the attached Example.

Regards,

Kaushik Solanki

Re: Calculation in load script using master calendar dates

sunny_talwar — Tue, 06 Sep 2016 23:17:31 GMT

Another alternative to Kaushik's solution

Table:

LOAD *,

((MonthEndDate - [Start Date])/([End Date] - [Start Date])) * Premium as ResultField;

LOAD *,

Date(Floor(MonthEnd([Start Date], IterNo()-1))) as MonthEndDate

While IterNo() <= Difference;

LOAD [Start Date],

[End Date],

(Year([End Date]) * 12 + Month([End Date])) - (Year([Start Date]) * 12 + Month([Start Date])) + 1 as Difference,

Premium,

ContractNo

FROM

[https://community.qlik.com/thread/231666]

(html, codepage is 1252, embedded labels, table is @1);

Re: Calculation in load script using master calendar dates

Anonymous — Wed, 07 Sep 2016 05:13:21 GMT

Dear kaushik.solanki‌ would you write your script in here since I have personal edition.

Thanks,

Re: Calculation in load script using master calendar dates

kaushiknsolanki — Wed, 07 Sep 2016 09:21:42 GMT

Here is the script.

DATA:

Load *,[Start Date]&'-'&[End Date] as Key;

LOAD Date(Date#([Start Date],'DD/MM/YYYY')) as [Start Date],

Date(Date#([End Date],'DD/MM/YYYY')) as [End Date],

Premium,

ContractNo

FROM

[https://community.qlik.com/thread/231666]

(html, codepage is 1252, embedded labels, table is @1);

MINMAX:

Load Min([Start Date]) as MIN,

Max([End Date]) as MAX

Resident DATA;

Let vMin = num(Peek('MIN',0,'MINMAX'));

Let vMax = num(Peek('MAX',0,'MINMAX'));

Temp:

Load Floor(MonthEnd(Date($(vMin) + RowNo() -1))) as MonthEnd

AutoGenerate 1

While Date($(vMin) + RowNo() -1) < Date($(vMax));

INTERVAL:

intervalmatch(MonthEnd)

Load [Start Date],[End Date] Resident DATA;

Left join(DATA)

Load distinct [Start Date]&'-'&[End Date] as Key,MonthEnd

Resident INTERVAL;

Drop table INTERVAL;

FINAL:

Load *,(MonthEnd - [Start Date]) / ([End Date] - [Start Date]) * Premium as Value Resident DATA;

Drop table DATA, MINMAX;

Regards,

Kaushik Solanki