Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
Hi
Do you want another field as payment date. If so,
in script, Load * , MonthStart(StartDate) as PaymentDate from table;
Hope it helps
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;
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
hey Amirvas,
not sure where is the Masster Calender table is. I am getting an error that table not found.
Thxs,
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
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,
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
Check to make sure there is a record in th Min and Max tables.
that's probably why it's not working
I checked them and there is one value in each of them.
Thxs,