Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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;
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
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.