how to split a row start date end date into months to calculate exposure

[st_rives]

Hello I need help for this toughy problem.

I receive file with this data : client id, start date, end date, premium amount. I want to split this line into months to calculate exposure : example :

input file :

a0001 15/01/2014 10/03/2014 55

output file :

a0001 15/01/2014 31/01/2014 17

a0001 01/02/2014 28/02/2014 28

a0001 01/03/2014 10/03/2014 10

This can be managed via a qvd file if necessary.

I know this can be easily fixed with SAS but I really need to do it with Qlikview.

thanks for you help.

[swuehl]

You can do something along these lines:

Set DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD * INLINE [

ID, Start, End, Amount

a0001, 15/01/2014, 10/03/2014, 55

];

TMP:

LOAD *, Monthname(Date) as Month;

LOAD *, Date(Start+iterno()-1) as Date Resident INPUT while Start+iterno()-1 <= End;

Left JOIN (TMP) LOAD ID, Month, count(Date) as MonthCount Resident TMP group by ID, Month;

Left JOIN (TMP) LOAD ID, count(Date) as TotalCount Resident TMP Group by ID;

drop table INPUT;

RESULT:

LOAD ID,

  Date(min(Date)) as Start,

  Date(max(Date)) as End, only(Amount) / only(TotalCount) *only(MonthCount) as Amount

Resident TMP group by ID, Month;

drop table TMP;

[MarcoWedel · Accepted solution]

Hi st.rives,

here is another solution:

tabInput:
LOAD * Inline [
ClientID, StartDate, EndDate, PremiumAmount
a0001, 15/01/2014, 10/03/2014, 55
a0002, 05/02/2014, 21/05/2014, 106
a0003, 03/03/2014, 10/08/2014, 161
a0004, 27/07/2014, 30/10/2014, 96
];

tabOutput:
LOAD *, Round(EndDateSplit-StartDateSplit+1) as PremiumAmountSplit;
LOAD
  ClientID,
  if(IterNo()=1,StartDate,Date(AddMonths(MonthStart(StartDate),IterNo()-1))) as StartDateSplit,
  Date(if(EndDate<Floor(MonthEnd(AddMonths(StartDate,IterNo()-1))),EndDate,Floor(MonthEnd(AddMonths(StartDate,IterNo()-1))))) as EndDateSplit
Resident tabInput
While AddMonths(MonthName(StartDate),IterNo()-1)<=MonthName(EndDate);

hope this helps also

regards

Marco

[st_rives]

Hello,

thank a lot for both of you for your quick replies with correct answers.

That's remarquable I'm really impressed.

I will keep Marco's solution that supplies all the need and even better as it gives a way to address this problem by creating a separate table linked on the clientId with the splitted months.

thank you very much your reply is really helpfull

[MarcoWedel]

you're welcome

regards

Marco