topic Re: How can I create a calendar with start /End Date ? in QlikView

How can I create a calendar with start /End Date ?

Fri, 14 Aug 2015 04:49:51 GMT

Hi all,

I have 2 dates fields like START_DATE, END_DATE. I would like to generate a calendar .

How can I do that ?

Thanks for your help.

Re: How can I create a calendar with start /End Date ?

sunny_talwar — Fri, 14 Aug 2015 04:55:23 GMT

Working off of Personal Edition, do you mind sharing the script you have in your attached qvw file?

Best,

Sunny

Re: How can I create a calendar with start /End Date ?

Fri, 14 Aug 2015 05:22:30 GMT

Hi Sunny

See attachment.

Re: How can I create a calendar with start /End Date ?

sunny_talwar — Fri, 14 Aug 2015 05:32:10 GMT

Not sure if this is what you want, but see if this helps:

TMP_CAL:

LOAD * INLINE [

CPD_ID, START_DATE, END_DATE

123ABC404,18/07/2014,13/04/2015

123ABC404,13/04/2015,

123ABC406,18/07/2014,13/04/2015

123ABC406,13/04/2015,

123ABC408,18/07/2014,13/04/2015

123ABC408,13/04/2015,

123ABC410,18/07/2014,13/04/2015

123ABC410,13/04/2015,

123ABC412,18/07/2014,13/04/2015

123ABC412,13/04/2015,

123ABC414,18/07/2014,13/04/2015

123ABC414,13/04/2015,

123ABC416,18/07/2014,13/04/2015

123ABC416,07/06/2010,18/06/2010

123ABC416,18/06/2010,27/07/2010

123ABC416,27/07/2010,28/10/2010

123ABC416,28/10/2010,02/11/2010

123ABC416,02/11/2010,09/02/2012

123ABC416,09/02/2012,09/02/2012

123ABC416,09/02/2012,14/03/2012

123ABC416,14/03/2012,19/06/2012

123ABC416,19/06/2012,10/09/2012

123ABC416,10/09/2012,03/07/2013

123ABC416,03/07/2013,07/03/2014

123ABC416,07/03/2014,28/05/2014

123ABC416,28/05/2014,30/06/2014

123ABC416,30/06/2014,29/08/2014

123ABC416,29/08/2014,17/09/2014

123ABC416,17/09/2014,22/09/2014

123ABC416,22/09/2014,23/09/2014

123ABC416,23/09/2014,24/09/2014

123ABC11028,24/09/2014,

123ABC11028,28/06/2013,01/07/2013

123ABC51282,01/07/2013,02/08/2013

123ABC51282,02/08/2013,06/08/2013

123ABC51282,06/08/2013,

123ABC51289,28/06/2013,01/07/2013

123ABC51289,01/07/2013,02/08/2013

123ABC51289,02/08/2013,30/10/2013

123ABC51289,30/10/2013,

123ABC51290,28/06/2013,01/07/2013

123ABC51290,01/07/2013,02/08/2013

123ABC51290,02/08/2013,12/08/2013

123ABC51290,12/08/2013,

123ABC51293,28/06/2013,01/07/2013

123ABC51293,01/07/2013,02/08/2013

123ABC51293,02/08/2013,05/08/2013

123ABC51293,05/08/2013,

123ABC51297,28/06/2013,01/07/2013

123ABC51297,01/07/2013,02/08/2013

123ABC51297,02/08/2013,07/08/2013

123ABC51297,07/08/2013,

123ABC51301,28/06/2013,01/07/2013

123ABC51301,01/07/2013,02/08/2013

123ABC51301,02/08/2013,08/08/2013

123ABC51301,08/08/2013,28/04/2015

123ABC51301,28/04/2015,28/04/2015

123ABC51301,28/04/2015,20/05/2015

123ABC51301,20/05/2015];

Temp:

Load min(START_DATE) as minDate,

max(END_DATE) as maxDate

Resident TMP_CAL;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Left Join(TMP_CAL)

IntervalMatch (Date)

LOAD START_DATE,

END_DATE

Resident TMP_CAL;

Re: How can I create a calendar with start /End Date ?

vikasmahajan — Fri, 14 Aug 2015 05:40:17 GMT

Refer this thread Default Date (current month&year) on calender object

Vikas

Re: How can I create a calendar with start /End Date ?

parthakk — Fri, 14 Aug 2015 06:20:20 GMT

Hi,

If m not wrong then u meant to have a single date field having start and end date as well. yet not losing the association between cpp_id,start date and end date...

PFA..

Thanks,

Partha Kulkarni

Re: How can I create a calendar with start /End Date ?

Fri, 14 Aug 2015 06:32:47 GMT

Thanks Sunny for your script.

Sorry, I forgot. If end_date is null , today , else end date.

When I select May 2014 I want to see all dates (START_DATE & END_DATE) in the month May 2014

Partha , your script is a good approach but when I select Apr 2015 I want to see all start_date & end_date in Apr 2015

Vika thanks for this reference.

Thanks,

Re: How can I create a calendar with start /End Date ?

sunny_talwar — Fri, 14 Aug 2015 06:38:01 GMT

See if this one is what you want:

TMP_CAL:

LOAD CPD_ID,

START_DATE,

Date(If(Len(Trim(END_DATE)) = 0, Today(), END_DATE)) as END_DATE;

LOAD * INLINE [

CPD_ID, START_DATE, END_DATE

123ABC404,18/07/2014,13/04/2015

123ABC404,13/04/2015,

123ABC406,18/07/2014,13/04/2015

123ABC406,13/04/2015,

123ABC408,18/07/2014,13/04/2015

123ABC408,13/04/2015,

123ABC410,18/07/2014,13/04/2015

123ABC410,13/04/2015,

123ABC412,18/07/2014,13/04/2015

123ABC412,13/04/2015,

123ABC414,18/07/2014,13/04/2015

123ABC414,13/04/2015,

123ABC416,18/07/2014,13/04/2015

123ABC416,07/06/2010,18/06/2010

123ABC416,18/06/2010,27/07/2010

123ABC416,27/07/2010,28/10/2010

123ABC416,28/10/2010,02/11/2010

123ABC416,02/11/2010,09/02/2012

123ABC416,09/02/2012,09/02/2012

123ABC416,09/02/2012,14/03/2012

123ABC416,14/03/2012,19/06/2012

123ABC416,19/06/2012,10/09/2012

123ABC416,10/09/2012,03/07/2013

123ABC416,03/07/2013,07/03/2014

123ABC416,07/03/2014,28/05/2014

123ABC416,28/05/2014,30/06/2014

123ABC416,30/06/2014,29/08/2014

123ABC416,29/08/2014,17/09/2014

123ABC416,17/09/2014,22/09/2014

123ABC416,22/09/2014,23/09/2014

123ABC416,23/09/2014,24/09/2014

123ABC11028,24/09/2014,

123ABC11028,28/06/2013,01/07/2013

123ABC51282,01/07/2013,02/08/2013

123ABC51282,02/08/2013,06/08/2013

123ABC51282,06/08/2013,

123ABC51289,28/06/2013,01/07/2013

123ABC51289,01/07/2013,02/08/2013

123ABC51289,02/08/2013,30/10/2013

123ABC51289,30/10/2013,

123ABC51290,28/06/2013,01/07/2013

123ABC51290,01/07/2013,02/08/2013

123ABC51290,02/08/2013,12/08/2013

123ABC51290,12/08/2013,

123ABC51293,28/06/2013,01/07/2013

123ABC51293,01/07/2013,02/08/2013

123ABC51293,02/08/2013,05/08/2013

123ABC51293,05/08/2013,

123ABC51297,28/06/2013,01/07/2013

123ABC51297,01/07/2013,02/08/2013

123ABC51297,02/08/2013,07/08/2013

123ABC51297,07/08/2013,

123ABC51301,28/06/2013,01/07/2013

123ABC51301,01/07/2013,02/08/2013

123ABC51301,02/08/2013,08/08/2013

123ABC51301,08/08/2013,28/04/2015

123ABC51301,28/04/2015,28/04/2015

123ABC51301,28/04/2015,20/05/2015

123ABC51301,20/05/2015];

Temp:

Load min(START_DATE) as minDate,

max(END_DATE) as maxDate

Resident TMP_CAL;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Left Join(TMP_CAL)

IntervalMatch (Date)

LOAD START_DATE,

END_DATE

Resident TMP_CAL;

Re: How can I create a calendar with start /End Date ?

parthakk — Fri, 14 Aug 2015 06:46:19 GMT

Hi i have loaded only four columns.. i had taken separate excel file. If u fill in all the columns then u shall hav all start and end dates.

Coming to your other part to hav today as end date for all null values u can us isnull() function in if condition i.e

if(isnull(END_DATE),Today,END_DATE) as END_DATE

Thanks,

Partha Kulkarni

Re: How can I create a calendar with start /End Date ?

Fri, 14 Aug 2015 15:55:38 GMT

Thank you for your help, guys.

Re: How can I create a calendar with start /End Date ?

sunny_talwar — Fri, 14 Aug 2015 16:21:44 GMT

‌If you got what you are looking for, I suggest marking the correct answer as well as any helpful answer to close this thread.

Best,

Sunny