topic Re: Find the month End Dates between the two dates in QlikView

Find the month End Dates between the two dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

I have a table comprises of three columns ID,start_date,End_Date.

ID Start_date end_Date

1 06/22/15 01/01/2016

2 03/03/16

My requirement is to have data in this format

ID Start_date end_Date Month_End_Date

1 06/22/15 01/10/2015 06/30/2015

1 06/22/15 01/10/2015 07/31/2015

1 06/22/15 01/10/2015 08/30/2015

1 06/22/15 01/10/2015 09/31/2015

2 03/03/16 03/31/2016

2 03/03/16 04/30/2016

Month End date column comprises of the Month end dates between start date and end date.

My final requirment is to map the months where ID is open

(Eg:ID as 1 is open in the month of June,july,august,September)

Guys pls help!!!

Re: Find the month End Dates between the two dates

Kushal_Chawda — Wed, 25 May 2016 07:32:56 GMT

may be this

Data:

ID,

Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd

Start_Date,

End_date

FROM Table

while Monthname(Start_Date)+iterno()-1 <= monthname(End_date);

Re: Find the month End Dates between the two dates

Anonymous — Wed, 25 May 2016 07:41:57 GMT

Thank you kushal,

What if end_date is null?

Re: Find the month End Dates between the two dates

Kushal_Chawda — Wed, 25 May 2016 08:00:37 GMT

I think that record will not be affected and will remain the same.

Re: Find the month End Dates between the two dates

Anonymous — Wed, 25 May 2016 09:07:44 GMT

thanks Kushal for your valuable time.Can you pls let me know the expression if my End_Date is null...as I need to calculate the count of months till the time ID is open.

Eg above mentioned ID 2 has start_date as 03/03/16

ID Start_Date End_Date Month _End_Date

2 03/03/16 03/31/16

2 03/03/16 04/30/16

2 03/03/16 05/31/16 (On 1st June,2016 if end_date is null)

Re: Find the month End Dates between the two dates

Anonymous — Wed, 25 May 2016 09:12:14 GMT

Thanks Kushal,this is what worked for me.

LOAD ID,

Start_Date,

Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd,

// AddMonths(MonthEnd(Start_Date),1) as add_month,

End_Date

FROM

...

while Monthname(Start_Date)+iterno()-1 <= if(IsNull(End_Date),monthname(today()),monthname(End_Date));

Re: Find the month End Dates between the two dates

Kushal_Chawda — Wed, 25 May 2016 09:45:13 GMT

Instead put this condition in script

Data:

LOAD ID,

Start_Date,

if(len(trim(End_Date))=0, today(),End_Date) as End_Date

// AddMonths(MonthEnd(Start_Date),1) as add_month,

FROM Source;

New:

LOAD *,

Monthend(Monthname(Start_Date)+iterno()-1) as MonthEnd

Resident Data

while Monthname(Start_Date)+iterno()-1 <= monthname(End_date);

Drop table Data;