Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i have a need to create a Daily Past Due report that shows percentage of Past Due ($)
I do not have a daily data set. But the data set contains date fields (FROM Date , TO Date). I am current;y expanding the data set to daily record in the script duplicating the records between the dates. Since I am dealing with large volumes of data I want to see if there is any other way to approach this logic instead of duplicating records at loan level. Thanks
LOAN_HIST:
SQL SELECT
EFF_FROM_DT,
EFF_TO_DT,
BALANCE,
substring(BRACCT,11,8) as LOAN_NBR,
DAYSPASTDUE,
PASTDUE,
CATEGORY
FROM PSAPROD.PSADDS."SHF_DLY_CORE_HSTRY" where
(EFF_FROM_DT >=TO_DATE('01-NOV-18','DD-MON-YY') and EFF_FROM_DT <=TO_DATE('26-NOV-18','DD-MON-YY'))
or
(EFF_TO_DT >=TO_DATE('01-NOV-18','DD-MON-YY') and EFF_TO_DT <=TO_DATE('26-NOV-18','DD-MON-YY'))
or
(EFF_TO_DT >=TO_DATE('31-DEC-9999','DD-MON-YYYY'))
order by LOAN_NBR,EFF_FROM_DT;
LOAN_HIST2:
LOAD
*,
Date(EFF_FROM_DT + IterNo() - 1) As Date
While EFF_FROM_DT + IterNo() - 1 <= EFF_TO_DT2;
LOAD *,
if(EFF_TO_DT='12/31/9999',if(BALANCE=0, EFF_FROM_DT, date(today())),if(BALANCE=0,EFF_FROM_DT,EFF_TO_DT)) as EFF_TO_DT2
Resident LOAN_HIST order by LOAN_NBR,EFF_FROM_DT;
drop table LOAN_HIST;
LOAN_HIST3:
load
*,
day(Date) as DayOfMonth,
Date(monthstart(Date), 'MMM-YY') as MonthYear,
((year(Date)*12)+month(Date)) - (((year(PASTDUE)*12)+month(PASTDUE))) as MonthDiff
resident LOAN_HIST2;
drop table LOAN_HIST2;
Hi,
Can you share some (made up) sample data & how you would like it to behave when you make selections (so assuming by creating daily data you want to be able to get a snapshot at any point in time of past due percentage?).
Cheers,
Chris.
I have attached sample qvw to the discussion. Thanks
Hi,
I am on personal edition so cannot open. If you pop some made up examples/samples of the data from your input columns;
EFF_FROM_DT,
EFF_TO_DT,
BALANCE,
substring(BRACCT,11,8) as LOAN_NBR,
DAYSPASTDUE,
PASTDUE,
CATEGORY
Into a spreadsheet I am happy to take a look.
Cheers,
Chris.
Attached Test_Data.xls to the post. Thanks
Hi,
So looking at your script & you data to get the balance to appear against each date between EFF_FROM_DT and EFF_TO_DT you can try using a date data island, although performance wise this can be slow, so depends on what you are doing with the rest of your model.
So in the top (the model with the date data island) you can see no balance for the selected loan on the days before the EFF_FROM_DT using If(Date<=EFF_TO_DT and Date>=EFF_FROM_DT, BALANCE,null()) and can use similar for the other fields/calculations.
Your calendar code could be as short as;
MasterCalendar:
LOAD
date(mindate + IterNo()) AS Date
WHILE mindate + IterNo() <= maxdate;
LOAD
min(FieldValue('EFF_FROM_DT', recno()))-1 as mindate,
max(FieldValue('EFF_TO_DT', recno())) as maxdate
AUTOGENERATE (1);
Cheers,
Chris.