topic Re: Trouble with Master Calendar Time Date in QlikView

Trouble with Master Calendar Time Date

provie98 — Wed, 26 Mar 2014 14:49:35 GMT

I'm trying to create a master calendar table to attached to an Add_Date that I have in my Core_Activity Table.

I have the following script to do this:

LET min_calendar_date = Num(Floor(Peek('Add_Date',0,'CORE_ACTIVITY')));
LET max_calendar_date = Num(Floor(Peek('Add_Date',-1,'CORE_ACTIVITY')));
LET varToday = num(today());

TempCalendar:
LOAD
$(min_calendar_date) + rowno() - 1 as Num,
date($(min_calendar_date) + rowno() - 1) as TempDate
AUTOGENERATE
$(max_calendar_date) - $(min_calendar_date) + 1;

Master_Calendar:

LOAD
TempDate as Add_Date,
Week(TempDate)as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
ceil(month(TempDate)/3) as Quarter,
'Q' & ceil(month(TempDate)/3) as QuarterName,
Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
inyeartodate(TempDate,$(varToday),0)*-1 As CurYTDFlag,
inyeartodate(TempDate,$(varToday),-1)*-1 as LastYTDFlag
RESIDENT TempCalendar

Order by TempDate ASC;
Drop Table TempCalendar;

The problem I'm encountering is that my Add_Date has a time on it. When I select Year off of the Master_Calendar table, it is only bringing back those dates time stamped of 12:00:00 AM. Any idea how i can get it to bring back all the dates for a specific Year regardless of the time? Any advice is greatly appreciated.

Thanks!

Re: Trouble with Master Calendar Time Date

Wed, 26 Mar 2014 14:56:15 GMT

Change the line

TempDate as Add_Date,

with

date(TempDate) as Add_Date,

Hope this helps

Re: Trouble with Master Calendar Time Date

its_anandrjs — Wed, 26 Mar 2014 14:59:52 GMT

Hi,

In your TempCalendar table in below line change date script like

date($(min_calendar_date) + rowno() - 1,'MM/DD/YYYY' ) as TempDate

put your region date format there

Hope this helps

Thanks & Regards

Re: Trouble with Master Calendar Time Date

its_anandrjs — Wed, 26 Mar 2014 15:02:27 GMT

Another way is change script in Master_Calendar

Master_Calendar:

LOAD
Date(TempDate,'MM/DD/YYY') as Add_Date,

Hope this Helps

Thanks & Regards

Re: Trouble with Master Calendar Time Date

provie98 — Wed, 26 Mar 2014 15:12:09 GMT

I tried all of the above suggestions and it's still not working. I appreciate the input though. If anyone else thinks of something let me know. Thanks!

Re: Trouble with Master Calendar Time Date

DavidFoster1 — Wed, 26 Mar 2014 15:29:10 GMT

In your Core_Activity table add an extra field (cal_date or somesuch) that is derived from the add_Date using:

daystart(add_date) note: dont use DATE function as that returns a string value.

Then use that date to build your calendar.

Re: Trouble with Master Calendar Time Date

Anonymous — Wed, 26 Mar 2014 15:38:39 GMT

The Master_Calendar script all looks fine to me, but I think David above is sort of on the correct track.

As he says add an extra field in your script to the Core_Activity table, I'd suggest :

num(floor(add_date)) as cal_date ,

Then in your Master_Calendar script change this line :

TempDate as Add_Date,

to this

TempDate as cal_date ,

After a reload check in your table viewer that Master_Calendar is joining to Core_Activity via cal_date and not by anything else.

Best Regards, Bill

Re: Trouble with Master Calendar Time Date

provie98 — Wed, 26 Mar 2014 15:48:00 GMT

Thanks so much for your help. This suggestion actually worked for me.