Calculation in load script using master calendar dates

[Anonymous]

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,

[kaushiknsolanki]

Hi,

Have a look at the attached Example.

Regards,

Kaushik Solanki

[sunny_talwar · Accepted solution]

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);

[Anonymous]

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

Thanks,

EE

[kaushiknsolanki]

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