Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
st_rives
Contributor II
Contributor II

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

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.

1 Solution

Accepted Solutions
MarcoWedel

Hi st.rives,

here is another solution:

QlikCommunity_Thread_111253_Pic1.JPG.jpg

QlikCommunity_Thread_111253_Pic2.JPG.jpg

QlikCommunity_Thread_111253_Pic3.JPG.jpg

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

View solution in original post

4 Replies
swuehl
MVP
MVP

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

Hi st.rives,

here is another solution:

QlikCommunity_Thread_111253_Pic1.JPG.jpg

QlikCommunity_Thread_111253_Pic2.JPG.jpg

QlikCommunity_Thread_111253_Pic3.JPG.jpg

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
Contributor II
Contributor II
Author

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