Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Creation of dates from particular date to till today.

In my task i want to load a script which has to create dates. Date should start from 01/01/2012 and end till today. For each date i need to find weekend, year, weekday of a date.

How could i achieve this.Any kind of help is appreciated.

1 Solution

Accepted Solutions
sunny_talwar

Try this to create dates:

LOAD Date(MakeDate(2012, 1, 1) + RecNo() - 1) as Date

AutoGenerate Today() - MakeDate(2012, 1, 1) + RecNo() + 1;

View solution in original post

16 Replies
sunny_talwar

Try this to create dates:

LOAD Date(MakeDate(2012, 1, 1) + RecNo() - 1) as Date

AutoGenerate Today() - MakeDate(2012, 1, 1) + RecNo() + 1;

ramoncova06
Specialist III
Specialist III

you can use a master calendar

The Master Calendar

Creating A Master Calendar

berryandcherry6
Creator II
Creator II
Author

Hi stalwar1

Thanks for reply,

Here Its creating only january month dates of every year till 2016. I need to display all 12 months of every year till 2016.

Regards,

Supriya

berryandcherry6
Creator II
Creator II
Author

Hi ramoncova06,

Thanks for your reply, My requirement is to generate date/calender from given date to till today.

Yes i went through link you have given but that is for mapping of tables(which is like joins in SQL),but how could i apply this concept to create dates.If you provide some code or sample regarding this will be really helpfull

Regards,

Supriya

Not applicable

Try with this one you just need to set your date on  the variables and on the first field of the table MasterCalendar.

Hope this work for you.

LET varMinDate = NUM(Peek('YourDate',0,'YourTable'));

LET varMaxDate = NUM(Peek('YourDate',-1,'YourTable'));

LET varToday = Num(today());

DateField:

LOAD

  $(varMinDate) + RowNo()-1 AS Num,

  date($(varMinDate) + rowno() -1) AS TempDate,

  rowno()

  AUTOGENERATE

  $(varMaxDate) - $(varMinDate) +1;

//Master Calendar

MasterCalendar:

LOAD

   TempDate AS YourDate,

   Week(TempDate) AS Week,

   Day(TempDate) AS Day,

   Year(TempDate) AS Year,

   Month(TempDate) AS Month,

   WeekDay(TempDate) AS WeekDay,

   'Q' & Ceil(Month(TempDate)/3) AS Quarter

Resident DateField

Order By TempDate ASC;

DROP TABLE DateField;

sunny_talwar

I am seeing all dates. Check the attached out

ramoncova06
Specialist III
Specialist III

here goes what I normally use 

LET vMinDate = '01/01/2012'

   LET vMaxDate = Today();

CalendarTemp:

LOAD $(vMinDate) + (RecNo()) + (IterNo()-1) AS AddedDate

AUTOGENERATE 1

WHILE Num($(vMinDate) + IterNo() -1) < Num($(vMaxDate));

Calendar:

load

date(AddedDate) as [Calendar Date],

Month(AddedDate) as [Calendar Month],

Year(AddedDate) as [Calendar Year],

WeekEnd(AddedDate) as [Calendar Week Ending],

weekyear(AddedDate) as [Calendar WeekYear#],

QuarterName(AddedDate) as [Calendar Year Quarter],

date(floor(MonthEnd(AddedDate))) as [Calendar Month End],

date(floor(MonthEnd(AddedDate,-1))) as [Previous Calendar Month End],

date(floor(MonthStart(AddedDate))) as [Calendar Month Start],

date(floor(MonthStart(AddedDate,-1))) as [Previous Calendar Month Start],

'Q' & Ceil(Month(AddedDate)/3) as [Calendar Quarter]

Resident CalendarTemp;

drop table CalendarTemp;

berryandcherry6
Creator II
Creator II
Author

Hi raul.marquez,

Thankyou for your reply,

In your answer you are mentioning to give date and tablename, why should i give tablename ,as i want to create list of dates ex: from 01/01/2012 to today().Date list  should be created without depending on any table.

Regards,

Supriya

berryandcherry6
Creator II
Creator II
Author

It showning me lines fetched is 0,and no values are being disaplyed. I have attached snapshot please look into that.Capture.PNG