topic Re: Daily Report from consolidated data set with date ranges - QV script in QlikView

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

phoenix — Tue, 04 Dec 2018 19:50:12 GMT

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;

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

chrismarlow — Tue, 04 Dec 2018 13:21:43 GMT

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.

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

phoenix — Tue, 04 Dec 2018 14:13:22 GMT

I have attached sample qvw to the discussion. Thanks

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

chrismarlow — Tue, 04 Dec 2018 17:11:21 GMT

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.

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

phoenix — Tue, 04 Dec 2018 19:51:05 GMT

Attached Test_Data.xls to the post. Thanks

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

chrismarlow — Thu, 06 Dec 2018 19:10:49 GMT

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.