topic Re: Calender in QlikView

Calender

Tue, 26 Aug 2014 04:41:28 GMT

hi friends...

i am new in qlikview. please help me for building calender as i have three tables and all three tables having date columns with different names so how to build a calender as i have to consider all three date consider...

Re: Calender

tresB — Tue, 26 Aug 2014 04:50:55 GMT

You should go through these blog posts:

canonical-date

the-master-calendar

Re: Calender

senpradip007 — Tue, 26 Aug 2014 04:58:10 GMT

Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.

tempMaxDate:

Load

Max(Date1) As MaxDate,

Min(Date1) As MinDate

Resident A;

Concatenate

Load

Max(Date2) As MaxDate,

Min(Date2) As MinDate

Resident B;

Concatenate

Load

Max(Date3) As MaxDate,

Min(Date3) As MinDate

Resident C;

MaxDate:

Load

Max(MaxDate) As MaxDate,

Min(MinDate) As MinDate

Resident tempMaxDate;

Drop Table tempMaxDate;

Let vMaxDate = Peek('MaxDate', 0, 'MaxDate');

Let vMinDate = Peek('MinDate', 0, 'MaxDate');

Cal:

LOAD

Year(Date($(vMinDate) + RecNo() - 1)) As Year,

Month(Date($(vMinDate) + RecNo() - 1)) AS Month,

'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,

Day($(vMinDate) + RecNo() - 1) AS Day,

Date($(vMinDate) + RecNo() - 1) AS CalendarDate

AutoGenerate vMaxDate - vMinDate +1 ;

Drop Table MaxDate;

Re: Calender

Tue, 26 Aug 2014 06:07:29 GMT

Hi Ashish

you can use below code for the Calendar also you can go threw with the blog on Qlickview. as per your requirement you can create calendar control.

see the below generic code for your review. that i was using in my application. let me know if it work for you ore else i can create as per your requirement.

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Num(MakeDate(2001,12,31));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS [Calendar Date],

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS Month,

num(Month(TempDate)) AS MonthNum,

Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate))) as CalendarYYYYMM,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS Year,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

Date(MonthStart(TempDate)) as CalendarMonthStart,

Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Calender

ashfaq_haseeb — Tue, 26 Aug 2014 06:31:31 GMT

Hi,

use below code

Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));


//*************** Temporary Calendar ***************
TempCalendar:
LOAD 
  $(varMinDate) + rowno() - 1 AS Num, 
  date($(varMinDate) + rowno() - 1) AS TempDate 
AUTOGENERATE 
  $(varMaxDate) - $(varMinDate) + 1; 




//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS Date,
  Week(TempDate) AS Week,
  WeekYear(TempDate) as TestWeek,
  Year(TempDate) AS Year,
  Month(TempDate) AS Month,
  year(TempDate)&'-'&Month(TempDate) as Year_Month,
  MonthName(TempDate) as YearMonth,
  Day(TempDate) AS Day,
  'Q'&ceil(month(TempDate)/3) AS Quarter

RESIDENT TempCalendar 
ORDER BY TempDate ASC;


DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);

Regards

ASHFAQ

Re: Calender

Tue, 26 Aug 2014 07:24:51 GMT

thanx Pradip , i have tried this one but its showing error message as-

Script line error:

cal:

LOAD

Year(Date( + RecNo() - 1)) As Year,

Month(Date( + RecNo() - 1)) AS Month,

'Q' & Ceil(Month(Date( + RecNo() - 1))/3) As Quarter,

Day( + RecNo() - 1) AS Day,

Date( + RecNo() - 1) AS CalendarDate

AutoGenerate vMaxDate - vMinDate +1

Re: Calender

ashfaq_haseeb — Tue, 26 Aug 2014 07:26:45 GMT

Hi,

did you try my code.

Regards

ASHFAQ

Re: Calender

Tue, 26 Aug 2014 07:33:45 GMT

ya, i have tried this one also but its showing wrong output...

Re: Calender

Tue, 26 Aug 2014 07:34:27 GMT

Thanx but, its showing wrong output...

Re: Calender

senpradip007 — Tue, 26 Aug 2014 07:35:16 GMT

Try with this:

Suppose you have three tables named A, B, C having three different date fields Date1, Date2, Date3.

tempMaxDate:

Load

Max(Date1) As MaxDate,

Min(Date1) As MinDate

Resident A;

Concatenate

Load

Max(Date2) As MaxDate,

Min(Date2) As MinDate

Resident B;

Concatenate

Load

Max(Date3) As MaxDate,

Min(Date3) As MinDate

Resident C;

MaxDate:

Load

Max(MaxDate) As MaxDate,

Min(MinDate) As MinDate

Resident tempMaxDate;

Drop Table tempMaxDate;

Let vMaxDate = Peek('MaxDate', -1, 'MaxDate');

Let vMinDate = Peek('MinDate', -1, 'MaxDate');

Cal:

LOAD

Year(Date($(vMinDate) + RecNo() - 1)) As Year,

Month(Date($(vMinDate) + RecNo() - 1)) AS Month,

'Q' & Ceil(Month(Date($(vMinDate) + RecNo() - 1))/3) As Quarter,

Day($(vMinDate) + RecNo() - 1) AS Day,

Date($(vMinDate) + RecNo() - 1) AS CalendarDate

AutoGenerate vMaxDate - vMinDate +1 ;

Drop Table MaxDate;

Re: Calender

Tue, 26 Aug 2014 08:57:38 GMT

sam error is coming....

Re: Calender

Tue, 26 Aug 2014 08:58:56 GMT

ya, i have tried that one also but wrong output is coming.....

Re: Calender

senpradip007 — Tue, 26 Aug 2014 09:01:17 GMT

Can you upload the sample app as its working fine at my side.

Re: Calender

Tue, 26 Aug 2014 12:12:18 GMT

suppose i have thses two tables having date fields as exam date and created date then how to build calender by considering these two different date field !

Re: Calender

Wed, 27 Aug 2014 07:20:57 GMT

Pradip Sen: i have tried this one also but above error is coming...

Re: Calender

rupaliqlik — Wed, 03 Sep 2014 17:27:47 GMT

External Calender Code

for

i=1994 to 1996

For j=1 to 12

Calender:

load

$(i) as EXT_YEAR,

$(j) as EXT_MONTH ,

(

makedate($(i),$(j),recno()))as ACTUAL_DATE ,

Week((makedate($(i),$(j),recno()))) AS EXT_WEEK,

recno() as EXT_DAY

autogenerate(31);

next

Re: Calender

evan_kurowski — Wed, 03 Sep 2014 18:28:53 GMT

Hello Ashish,

I see 'DD-MMM-YY' and 'DD-MM-YYYY' formats and I'm guessing the script header probably has something like 'D/M/YYYY' as the default. Perhaps it is something in these varying formats that is preventing a capture of the beginning and end-point across the three sources, because otherwise the suggestions of code which loop between a min-max combo and fill in the date range are all standard and proven segments.

Maybe you should post the actual spreadsheet in this thread, or a reduced sample of it. Once the contributors here have the actual spreadsheet, I'm sure someone will hook it up to a .QVW that demonstrates a proven solution.