topic Re: Mastercalender to date column mapping in QlikView

Mastercalender to date column mapping

Tue, 09 Feb 2016 14:51:00 GMT

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?

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.

Re: Mastercalender to date column mapping

hic — Tue, 09 Feb 2016 15:02:36 GMT

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

HIC

Re: Mastercalender to date column mapping

Tue, 09 Feb 2016 15:08:43 GMT

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?

Re: Mastercalender to date column mapping

Mark_Little — Tue, 09 Feb 2016 15:14:14 GMT

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;

Re: Mastercalender to date column mapping

Tue, 09 Feb 2016 15:23:00 GMT

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?