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

[kaushiknsolanki]

Hi,

     I couldnt understand where is your problem.

     Kindly explain with example.

Regards,

Kaushik Solanki

[#3]

Sorry for not being clear.

My problem is that the above code does not work. Im trying to create a master calendar with the REPORT_DATE as earliest and latest date, but every time I reload Im given the following error:

Script line error:

Calendar_tmp:

LOAD

          RowNo()          +  - 1 as TempDate

AutoGenerate  -  + 1

Table not found

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

Table not found

DROP TABLES statement

[kaushiknsolanki]

Hi,

     What error it gives.

Regards,

Kaushik Solanki

[#5]

Hi Kaushik Solanki

Im given the error the following error

Script line error:

Calendar_tmp:

LOAD

          RowNo()          +  - 1 as TempDate

AutoGenerate  -  + 1

Table not found

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

Table not found

DROP TABLES statement

Regards,

Philip Hoyos

[kaushiknsolanki]

Hi,

     From your script it doesnt seem anything wrong to give this error.

     But make sure that you dont have any other table with just one field called Temp_Date.

     And one more thing you can do is use the debug option to know at what line of the script you are getting error.

Regards,

Kaushik Solanki

[#7]

Hmm something must be wrong.

When I try removing the last bit, so that I can see what valuables the variables contain, they do not seem to contain anything. Im guessing that there is a problem with the way I try to create the variables and get the Minimum and Maximum values.

Is this correct?

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;

[kaushiknsolanki]

Try this.

Min_Max:

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

         num(floor(max(REPORT_DATE))) as MaxDate

Resident TICKET;

Regards,

Kaushik Solanki

[#9]

Well that was very helpful Now I get a number of the date in the table. How do I convert that into a date and time format that I can use? Like this: DD-MM-YYYY HH:MM?

Thank you very much.

Regards,

Philip Hoyos

[kaushiknsolanki]

Hi,

     Yes, but here the HH:MM will be always 00:00

Regards,

Kaushik Solanki