topic Re: Master Calendar with MinDate and MaxDate from table in QlikView

Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 08:15:22 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

kaushiknsolanki — Tue, 02 Jul 2013 08:21:24 GMT

Hi,

I couldnt understand where is your problem.

Kindly explain with example.

Regards,

Kaushik Solanki

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 08:24:10 GMT

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

kaushiknsolanki — Tue, 02 Jul 2013 08:27:09 GMT

Hi,

What error it gives.

Regards,

Kaushik Solanki

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 08:33:37 GMT

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

kaushiknsolanki — Tue, 02 Jul 2013 08:44:22 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 08:55:22 GMT

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

kaushiknsolanki — Tue, 02 Jul 2013 09:05:27 GMT

Try this.

Min_Max:

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

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

Resident TICKET;

Regards,

Kaushik Solanki

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 09:10:51 GMT

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

kaushiknsolanki — Tue, 02 Jul 2013 09:12:19 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 09:26:14 GMT

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;

Re: Master Calendar with MinDate and MaxDate from table

kaushiknsolanki — Tue, 02 Jul 2013 09:28:32 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 09:40:43 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

kamalqlik — Tue, 02 Jul 2013 09:40:49 GMT

Hi,

Just try to use it

LOAD

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

Year(TempDate) as Year,

and so on

Regards

Kamal

Re: Master Calendar with MinDate and MaxDate from table

kamalqlik — Tue, 02 Jul 2013 09:42:33 GMT

Can you please share the snapshot for the error.

or please explain me the output you want

Regards

Kamal

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 09:44:20 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

kaushiknsolanki — Tue, 02 Jul 2013 09:51:08 GMT

Try this.

LOAD

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

AutoGenerate 1

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

Regards,

Kaushik Solanki

Re: Master Calendar with MinDate and MaxDate from table

kamalqlik — Tue, 02 Jul 2013 09:53:22 GMT

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);

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

Re: Master Calendar with MinDate and MaxDate from table

Tue, 02 Jul 2013 09:59:13 GMT

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

Re: Master Calendar with MinDate and MaxDate from table

er_mohit — Tue, 02 Jul 2013 10:03:53 GMT

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