topic Re: Master Calendar Problem in QlikView

Master Calendar Problem

Thu, 17 Mar 2016 16:21:56 GMT

Hi, i am new to Qlikview. I have created a master calendar but year column which contains 2 years data (2011 & 2012) is not working properly. It's providing same result for both years on selecting a particular year.

VisitedSites:

LOAD Agency,

Platform,

Url,

[Date Sampled],

date(floor(timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Date],

time(frac (timestamp#([Date Sampled],'MM/DD/YYYY hh:mm'))) as [Record_Time],

[Likes/Followers/Visits/Downloads]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)where Platform <> 'TOTAL' ;

Temp:

Load

min([Record_Date]) as minDate,

max([Record_Date]) as maxDate

Resident VisitedSites;

Let varMinDate = '8/1/2011';

Let varMaxDate ='12/31/2012';

DROP Table Temp;

Datefield:

LOAD

date(date#('$(varMinDate)')-1 + recno()) as Datefield

AUTOGENERATE (date#('$(varMaxDate)')-date#('$(varMinDate)'))+1;

Calendar:

LOAD

Datefield as %Date,

date(Datefield,'YYYY-MMM-DD') as Date,

year(Datefield) as Year,

month(Datefield) as Month,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday,

'Q' & ceil(month(Datefield) / 3) AS Quarter,

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

dual((Month(Datefield) &'-'&Year(Datefield)),num(MakeDate(year(Datefield),month(Datefield)))) as Monthyear,

ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

yearname (Datefield, 0, 10 ) as FiscalYear

RESIDENT Datefield;

drop table Datefield;

Re: Master Calendar Problem

swuehl — Thu, 17 Mar 2016 18:25:56 GMT

What is the key field between your calendar and VisitedSites table?

If there is no link, selections in calendar will have no effect on your other tables at all.

Re: Master Calendar Problem

sunny_talwar — Thu, 17 Mar 2016 18:26:02 GMT

Your date field from calendar isn't linked to the date field in your VisitedSites table. Try this:

Calendar:

LOAD

Datefield as [Record_Date],

date(Datefield,'YYYY-MMM-DD') as Date,

year(Datefield) as Year,

month(Datefield) as Month,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday,

'Q' & ceil(month(Datefield) / 3) AS Quarter,

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

dual((Month(Datefield) &'-'&Year(Datefield)),num(MakeDate(year(Datefield),month(Datefield)))) as Monthyear,

ApplyMap('QuartersMap', month(AddMonths(Datefield,3)), Null()) as FiscalQuarter,

yearname (Datefield, 0, 10 ) as FiscalYear

RESIDENT Datefield;

drop table Datefield;

Re: Master Calendar Problem

rubenmarin — Thu, 17 Mar 2016 18:26:34 GMT

Hi Manisha, you need to link your calendar to the VisitedSites table.

Tables are linked when they have fields with the same name,to make it simpler and don't change anything you already loaded, you can create another field in your calendar to link the tables:

Calendar:

LOAD

Datefield as %Date,

date(Datefield,'YYYY-MMM-DD') as Date,

date(floor(timestamp#(Datefield,'MM/DD/YYYY hh:mm'))) as [Record_Date],

date(Datefield) as [Record_Date]

...

Edit: Trying to keep it simple I made it wrong, Sunny is right, the timestamp# will make my script to fail

Re: Master Calendar Problem

Fri, 18 Mar 2016 15:34:47 GMT

Thank you all for correcting me. I would like to know what is the best scenario to create variables for dates. As my data as no values for whole year in 2011.