Master Calendar with MinDate and MaxDate from table

[#1]

Im attempting to create a master calendar, where

• MinDate = the earliest date from the Column 'REPORT_DATE' in the Table 'TICKET' and
• MaxDate = the latest date from the Column 'REPORT_DATE' in the Table 'TICKET'

Each entry in the Column 'REPORT_DATE' is formated like this"DD-MM-YYYY HH:MM"

I have tried the following

Min_Max:
LOAD
          Date(Floor(Min(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MinDate,
          Date(Floor(Max(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MaxDate
Resident TICKET;

and then created the vaiables and afterwards dropping the above table as well as creating a temp table

LET vMinDate = Peek('MinDate', 0, 'Min_Max');
LET vMaxDate = Peek('MaxDate', 0, 'Min_Max');
DROP Table Min_Max;
Calendar_tmp:
LOAD
          RowNo()          + $(vMinDate) - 1 as TempDate
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

Here after creating the master calendar

MasterCalendar:
LOAD
          Date(TempDate) as Date,
          Year(TempDate) as Year,
          Month(TempDate) as Month,
          Day(TempDate) as Day,
          Week(TempDate) as Week,
          Weekday(TempDate) as Weekday,
          'Q' & Ceil(Month(TempDate)/3) as Quarter,
          Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,
          Year(TempDate) & '-' & Week(TempDate) as YearWeek
RESIDENT Calendar_tmp;
DROP Table Calendar_tmp;

Im still very new to QlikView, so my attempt has been assembled from various sources and what I could find on the forum, so if you can provide comments with explanation to what Im doing wrong and what I should do instead, it would help me a lot. Thank you very much for your time and help

Sources

• http://community.qlik.com/servlet/JiveServlet/previewBody/3102-102-3-4191/DateFields-TechBrief-US_v2...
• http://community.qlik.com/thread/24869
• http://community.qlik.com/message/174375

[#2]

Can you help me how I can accompish that from the text that I get?

In the original table I have the format DD-MM-YYYY HH:MM:SS, and now i have it as a number. How do I get the timestamp in QlikView.

I have tried the following, but the timestamp is not correct.

MasterCalendar:

LOAD

          Date(TempDate) as Date,

          Year(TempDate) as Year,

          Month(TempDate) as Month,

          Day(TempDate) as Day,

          Week(TempDate) as Week,

          Weekday(TempDate) as Weekday,

          'Q' & Ceil(Month(TempDate)/3) as Quarter,

          Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,

          Year(TempDate) & '-' & Week(TempDate) as YearWeek,

          Timestamp(TempDate, 'HH:MM:SS') as TimeStamp

RESIDENT Calendar_tmp;

[kaushiknsolanki]

Do one thing. to get the HH and SS try below code.

Min_Max:

Load num((min(REPORT_DATE))) as MinDate,

         num((max(REPORT_DATE))) as MaxDate

Resident TICKET;

This code will keep the HH and SS value, then you can use your script to get the timestamp also.

Regards,

Kaushik Solanki

[#4]

Thank you, Im getting very close I can tell. I think Im facing the last problem now.

When Im reloading the code I have a problem with the following code:

Calendar_tmp:

LOAD

          RowNo()          + $(vMinDate) - 1 as TempDate

AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

I expect that it is because of the decimals that comes with the variables. What would be the best way to overcome that problem?

Thank you very much for your time.

Regards,
Philip Hoyos

[kamalqlik]

Hi,

Just try to use it

LOAD

          Date(TempDate,'DD/MM/YYYY') as Date,

          Year(TempDate) as Year,

and so on

Regards

Kamal

[kamalqlik]

Hi

Can you please share the snapshot for the error.

or please explain me the output you want

Regards

Kamal

[#7]

That does not work

Im given the follwing error:

Garbage after statement

Calendar_tmp:

LOAD

          RowNo()          + 40606,569710648 - 1 as TempDate

AutoGenerate 41446,319421296 - 40606,569710648 + 1

Also If I do not create the above table, I will not have a TempDate to use.

Regards,

Philip Hoyos

[kaushiknsolanki]

Try this.

LOAD

          timestamp(RowNo()          + $(vMinDate) - 1) as TempDate

AutoGenerate 1

where   num(timestamp(RowNo()          + $(vMinDate) - 1)) < $(vMaxDate);

Regards,

Kaushik Solanki

[kamalqlik]

Hi,

Try using loop.

SET StartYear = 2010;

SET YearsToGenerate = 3;

FOR i = 1 TO YearsToGenerate;

          LET curYear    = StartYear + ($(i) - 1);

          LET StartDate  = makedate(curYear);                    //Returns first day of year

          LET YearEnd    = yearend(StartDate);          //Returns last day of year

          LET EndWeek    = weekend(StartDate);

          LET DayEndWeek = day(EndWeek);

          //Calculate number of days in the year

          LET TotalDays = (YearEnd - StartDate) + 2;

          //Create a temporary calendar

          TempCalendar:

          LOAD

                    recno()*$(i) AS DateKey,

                    date('$(StartDate)' + recno() - 1, 'DD/MM/YYYY') AS PeriodDate

          AUTOGENERATE(TotalDays - 1);

NEXT

MasterCalendar:

LOAD

          date(PeriodDate, 'DD/MM/YYYY') AS PeriodDate,

          year(PeriodDate) AS Year,

          month(PeriodDate) AS Month,

          num(month(PeriodDate)) AS MonthLink,

          week(PeriodDate) AS Week,

          day(PeriodDate) AS Day,

RESIDENT TempCalendar;

ORDER BY PeriodDate asc;

Hope it helps you

Regards

Kamal

[#10]

The error I get when trying your suggestion:

Garbage after statement

Calendar_tmp:

LOAD

          timestamp(RowNo()          + 40606,569710648 - 1) as TempDate

AutoGenerate 1

where   num(timestamp(RowNo()          + 40606,569710648 - 1)) < 41446,319421296

I think the problem is that Im trying to create rows using a decimal number, and that wont work right now. So the question is how to accomplish generating rows from the MinDate to the MaxDate, and till keeping the decimals to be used with timestamp.

The output Im trying to get from this is to be used In the Master Calendar where I would like a row where I have the Time in the format HH:MM:SS

[er_mohit]

to get minimum timestamp try this

Timestamp(timesatmp#(TempDate, 'DD-MM-YYYY 'hh:mm:ss'),'hh:mm:ss') as TimeStamp

or in yout above code

try to use time format in small letter

hope it helps