Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Master Calendar with MinDate and MaxDate from table

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

21 Replies

Re: Master Calendar with MinDate and MaxDate from table

Hi,

     I couldnt understand where is your problem.

     Kindly explain with example.

Regards,

Kaushik Solanki

Not applicable

Re: Master Calendar with MinDate and MaxDate from table

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

Re: Master Calendar with MinDate and MaxDate from table

Hi,

     What error it gives.

Regards,

Kaushik Solanki

Not applicable

Re: Master Calendar with MinDate and MaxDate from table

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

Re: Master Calendar with MinDate and MaxDate from table

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

Not applicable

Re: Master Calendar with MinDate and MaxDate from table

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;

Re: Master Calendar with MinDate and MaxDate from table

Try this.

Min_Max:

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

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

Resident TICKET;

Regards,

Kaushik Solanki

Not applicable

Re: Master Calendar with MinDate and MaxDate from table

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

Re: Master Calendar with MinDate and MaxDate from table

Hi,

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

Regards,

Kaushik Solanki

Community Browser