In a need to create Payment schedule

[alec1982]

Hi guys,

I am having hard time creating a payment scehdule table based on the following example:

ID          StartDate          EndDate          Amount

1          2010/09/15         2013/10/8           500

2          2009/03/01         2015/03/31         300

3          2011/04/01         2011/04/01          200

I need to create a Payment date which should start on the first day of every month as the following example:

ID     StartDate          EndDate          PayementDate

1

2010//09/15

2013/10/08

2010/09/01

1

2010//09/15

2013/10/08

2010/10/01

1

2010//09/15

2013/10/08

2010/11/01

And so on to the last month.

Anybody can help Please.

Thxs,

[MayilVahanan]

Hi

     Do you want another field as payment date. If so,

     in script, Load * , MonthStart(StartDate) as PaymentDate from table;

Hope it helps

[avastani]

Create a basic Calendar table with Monthstart() of every month.

Next do an intervalMatch with the Fact data

tempInvervalTable:

LOAD ID, StartDate, EndDate, Amount FROM MyFactFile.fileext;

Fact:

LOAD MonthStart(Date) AS PaymentDate

RESIDENT MasterCalendar;

LEFT JOIN (Fact)

INTERVALMATCH (PaymentDate)

LOAD StartDate, EndDate

RESIDENT tempIntervalTable;

LEFT JOIN (Fact)

LOAD * RESIDENT tempIntervalTable;

DROP TABLE tempIntervalTable;

[alec1982]

Hi,

thank you for the reply, this doesn't give me the right dates. it gives me the firstdate and the last one.

If you look at the period for ID1 the payment Date should be as follow as a result:

Payment Date

2010/09/01

2010/10/01

2010/11/01

....

...

..

...

2013/10/01

[alec1982]

hey Amirvas,

not sure where is the Masster Calender table is. I am getting an error that table not found.

Thxs,

[avastani]

You need to create MasterCalendar. MasterCalendar can be from the fact data you have.

Min:

LOAD Min(StartDate) AS MinDate

RESIDENT tempIntervalTable;

Max:

Load Max(EndDate) AS MaxDate

RESIDENT tempIntervalTable;

Let varMinDate = Num(Peek(‘MinDate’, 0, ‘Min’));

Let varMaxDate = Num(Peek(‘MaxDate’, 0, ‘Max));

Then you create your MasterCalendar table from these two variable for all the dates in between

MasterCalendar:

LOAD

$(varMinDate) AS NumDate

Date($(varMinDate)) AS PaymentDate

AUTOGENERATE($(varMaxDate) - $(varMinDate) + 1);

Then your interval joins..

Thanks.

Amir.

=====================

Amirali Vastani

Tel: 646.773.7936

[alec1982]

hey Amir,

Thanks for your help.

I am getting the following error when the script gets to the Master Calendar:

Syntax error, missing/misplaced FROM:

MasterCalendar:

LOAD     AS NumDate,

    Date() AS PaymentDate

    AUTOGENERATE( -  + 1)

MasterCalendar:

LOAD     AS NumDate,

    Date() AS PaymentDate

    AUTOGENERATE( -  + 1)

Looks like it is not recognizing the varriable?

Thxs,

[avastani]

LET varMinDate = Num(Peek('StartDate', 0, 'Min'));

LET varMaxDate = Num(Peek('EndDate', 0, 'Max'));

Fact:

LOAD

       $(varMinDate) + rowno() - 1 AS NumDate,

       date($(varMinDate) + rowno() - 1) AS PaymentDate

AUTOGENERATE

       $(varMaxDate) - $(varMinDate) + 1;

Thanks.

Amir.

=====================

Amirali Vastani

Tel: 646.773.7936

[avastani]

Check to make sure there is a record in th Min and Max tables.

that's probably why it's not working

[alec1982]

I checked them and there is one value in each of them.

Thxs,