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

[#1]

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.

[sunny_talwar]

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

Best,

Sunny

[#3]

Hi Sunny

See attachment.

[sunny_talwar]

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;

[vikasmahajan]

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

Vikas

[parthakk]

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

[#7]

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,

[sunny_talwar · Accepted solution]

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;

[parthakk]

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

[#10]

Thank you for your help, guys.