Daily Report from consolidated data set with date ranges - QV script

[phoenix]

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;

[chrismarlow]

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.

[phoenix]

I have attached sample qvw to the discussion. Thanks

[chrismarlow]

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.

[phoenix]

Attached Test_Data.xls to the post. Thanks

[chrismarlow]

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.