topic Re: In a need to create Payment schedule in QlikView

In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 16:41:53 GMT

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

2010//09/15

2013/10/08

2010/09/01

2010//09/15

2013/10/08

2010/10/01

2010//09/15

2013/10/08

2010/11/01

And so on to the last month.

Anybody can help Please.

Thxs,

Re: In a need to create Payment schedule

MayilVahanan — Thu, 30 Aug 2012 16:55:11 GMT

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

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

Hope it helps

Re: In a need to create Payment schedule

avastani — Thu, 30 Aug 2012 17:32:43 GMT

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;

Re: In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 18:21:49 GMT

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

Re: In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 18:32:39 GMT

hey Amirvas,

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

Thxs,

Re: In a need to create Payment schedule

avastani — Thu, 30 Aug 2012 18:37:32 GMT

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

Re: In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 19:22:55 GMT

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,

Re: In a need to create Payment schedule

avastani — Thu, 30 Aug 2012 19:26:08 GMT

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

Re: In a need to create Payment schedule

avastani — Thu, 30 Aug 2012 19:26:38 GMT

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

that's probably why it's not working

Re: In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 19:28:08 GMT

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

Thxs,

Re: In a need to create Payment schedule

avastani — Thu, 30 Aug 2012 19:29:08 GMT

Test the variables to ensure there is a value in them

Thanks.

Re: In a need to create Payment schedule

alec1982 — Thu, 30 Aug 2012 19:42:47 GMT

I guess that i have fixed it a little differently:

Table1:

Load ID,

StartDate,

EndDate,

AddMonths(StartDate,iterno()-1) as [Payment Date]

From Source file;

Payment Schedule:

Load ID,

StartDate,

EndDate,

MonthStart([Payment Date]) as PaymentDate

Resident table1

Thank you so much for your help.