Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Mastercalender to date column mapping

Hi,

I have created a master calender for the date range i have data. I have data of tweets.i want a bar chart having dimension of all the days in a week and another one with month names. it should measure  the no of tweets in a week and month. how do i link this to master calendar?

p1.png

This is my new date column which i want to link to master calendar. the bar graph should show height 1 on 02/05/2016 and height zero for 01/04/2016 since there is 02/04/2016 in this newdate column.

Tags (2)
4 Replies
Employee
Employee

Re: Mastercalender to date column mapping

Just make sure that your master calendar contains the field "newdate", a Month field and a Week field, and it should work automatically.

HIC

Not applicable

Re: Mastercalender to date column mapping

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

             min(newdate) as minDate, 

               max(newdate) as maxDate

Resident  root; 

Let varMinDate = num(makedate(2014,02,08));

//Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

//Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

//LET varMaxDate=floor(monthend(today()));

LET varMaxDate=floor(today());

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

MasterCalendar: 

Load 

               TempDate AS OrderDate, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

how to add new date in master calender?

mark6505
Valued Contributor III

Re: Mastercalender to date column mapping

Hi,

Range:

LOAD

  min([newdate]) as startdate,

  max([newdate]) as enddate

resident YourTable';

//Peek out the values for later use

let vStart = peek('startdate',-1,'Range')-1;

let vEnd = peek('enddate',-1,'Range');

let vRange = $(vEnd) - $(vStart);

//Remove Range table as no longer needed

Drop table Range;

//Generate a table with a row per date between the range above

Date:

Load

  $(vStart)+recno() as Date

autogenerate $(vRange);

//Calculate the Parts you need to examine

Calendar:

load

  Date as [newdate],

  Year(Date) as CalendarYear,

  Month(Date) as Cal_Month,

  Day(Date) as Cal_Day,

  Week(Date) as Cal_Week,

  Weekday(Date) as Cal_WeekDay

resident Date;

//Tidy up

Drop table Date;

Highlighted
Not applicable

Re: Mastercalender to date column mapping

i did this, it worked. Now can i get bar chart having dimension as all months in calendar and measure of the height of bars for only those dates which have entry in the new date field and rest of the bars height zero?

Community Browser