Calendar help

[#1]

I'm trying to get my first master calendar to work. I found some script on this community but I guess I'm not completely sure how it all works. I'm trying to insert it to work with a current .qvw that I have.

I have the two attached my tabs.

I realize this could be a loaded question. But Any help getting this to mold would be great. I think after doing it once and knowing what to replace and where to replace it I'll get the hang of it.

Thank you!

[Anonymous]

Firstly, the date field in your Calendar table is CalendarDate, but will not have anything to link to in the rest of your script.  It should be renamed to status_date to match the field in your History table.

Secondly, you won't need the calendar controls you created in the History table.  You should now be creating those in the Calendar table.

Go into Settings --> Document Properties --> General and make sure that "Generate Logfile" is checked.

Then run your script and if there is an error you can check the reload log file that should be in the same directory as the qvw.  This will tell what else is going wrong.

[#3]

6/18/2012 10:37:57 AM: 0002  SET ThousandSep=','
6/18/2012 10:37:57 AM: 0003  SET DecimalSep='.'
6/18/2012 10:37:57 AM: 0004  SET MoneyThousandSep=','
6/18/2012 10:37:57 AM: 0005  SET MoneyDecimalSep='.'
6/18/2012 10:37:57 AM: 0006  SET MoneyFormat='$#,##0.00;($#,##0.00)'
6/18/2012 10:37:57 AM: 0007  SET TimeFormat='h:mm:ss TT'
6/18/2012 10:37:57 AM: 0008  SET DateFormat='M/D/YYYY'
6/18/2012 10:37:57 AM: 0009  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'
6/18/2012 10:37:57 AM: 0010  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
6/18/2012 10:37:57 AM: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
6/18/2012 10:37:57 AM: 0014  OLEDB CONNECT*Provider*
6/18/2012 10:37:57 AM: 0015  Learners:
6/18/2012 10:37:57 AM: 0016 
6/18/2012 10:37:57 AM: 0017  LOAD learner_id,
6/18/2012 10:37:57 AM: 0018    1 as learnerIDCount,
6/18/2012 10:37:57 AM: 0019      "last_name" as 'Learner Last Name',
6/18/2012 10:37:57 AM: 0020      "first_name" as 'Learner First Name',
6/18/2012 10:37:57 AM: 0021      title as 'Learner Title',
6/18/2012 10:37:57 AM: 0022      if (dept = 'WORK COMP','WORKCOMP',dept) as deptkey,
6/18/2012 10:37:57 AM: 0023      status as 'Learner Status',
6/18/2012 10:37:57 AM: 0024      if (user3 like 'eic*'  or user3 = 'E' or user3 = 'eix' or  user3 like 'ELECTRIC*',
6/18/2012 10:37:57 AM: 0025      'ELECTRIC INSURANCE',
6/18/2012 10:37:57 AM: 0026      if (user3 like 'se*' ,'SEDGWICK JAMES', user3)) as 'Learner Company',
6/18/2012 10:37:57 AM: 0027      ManagerID as 'Learner Manager ID'
6/18/2012 10:37:57 AM: 0028  SQL SELECT
6/18/2012 10:37:57 AM: 0029  learner_id,
6/18/2012 10:37:57 AM: 0030  "last_name",
6/18/2012 10:37:57 AM: 0031      "first_name",
6/18/2012 10:37:57 AM: 0032      title,
6/18/2012 10:37:57 AM: 0033      dept,
6/18/2012 10:37:57 AM: 0034      status,
6/18/2012 10:37:57 AM: 0035      user3,
6/18/2012 10:37:57 AM: 0036      ManagerID
6/18/2012 10:37:57 AM: 0037  FROM "LWEB_EI".dbo.learners

6/18/2012 10:37:57 AM:        9 fields found: learner_id, learnerIDCount, Learner Last Name, Learner First Name, Learner Title, deptkey, Learner Status, Learner Company, Learner Manager ID, 1,550 lines fetched
6/18/2012 10:37:58 AM: 0040  Directory
6/18/2012 10:37:58 AM: 0041  LEFT JOIN (Learners)
6/18/2012 10:37:58 AM: 0042 
6/18/2012 10:37:58 AM: 0043  LOAD
6/18/2012 10:37:58 AM: 0044      description as 'Learner Department',
6/18/2012 10:37:58 AM: 0045      val as deptkey
6/18/2012 10:37:58 AM: 0046  SQL SELECT
6/18/2012 10:37:58 AM: 0047      description,
6/18/2012 10:37:58 AM: 0048      val
6/18/2012 10:37:58 AM: 0049  FROM "LWEB_EI".dbo.codes 
6/18/2012 10:37:58 AM: 0050  where lucode = 'learner_dept'
6/18/2012 10:37:58 AM:        2 fields found: Learner Department, deptkey, 20 lines fetched
6/18/2012 10:37:58 AM:       Joining/Keeping
6/18/2012 10:37:58 AM: 0055  History:
6/18/2012 10:37:58 AM: 0056  QUALIFY *
6/18/2012 10:37:58 AM: 0057  UNQUALIFY learner_id
6/18/2012 10:37:58 AM: 0058  LOAD "learner_id",
6/18/2012 10:37:58 AM: 0059      "course_no",
6/18/2012 10:37:58 AM: 0060      title,
6/18/2012 10:37:58 AM: 0061      IF (status='e' or status = 'E','Enrolled',
6/18/2012 10:37:58 AM: 0062      IF (status='F' or status = 'f','Finished', status)) as Course_Status,
6/18/2012 10:37:58 AM: 0063      "status_date",
6/18/2012 10:37:58 AM: 0064 
6/18/2012 10:37:58 AM: 0065 
6/18/2012 10:37:58 AM: 0066 
6/18/2012 10:37:58 AM: 0067 
6/18/2012 10:37:58 AM: 0068      duration
6/18/2012 10:37:58 AM: 0069  SQL SELECT "learner_id",
6/18/2012 10:37:58 AM: 0070      "course_no",
6/18/2012 10:37:58 AM: 0071      title,
6/18/2012 10:37:58 AM: 0072      status,
6/18/2012 10:37:58 AM: 0073      "status_date",
6/18/2012 10:37:58 AM: 0074      duration
6/18/2012 10:37:58 AM: 0075  FROM "LWEB_EI".dbo.history
6/18/2012 10:37:58 AM: 0076  where duration is not null
6/18/2012 10:37:58 AM:        6 fields found: learner_id, History.course_no, History.title, History.Course_Status, History.status_date, History.duration, 83,387 lines fetched
6/18/2012 10:37:59 AM: 0080  LET vDateMin = Num(MakeDate(2009,1,1))
6/18/2012 10:37:59 AM: 0081  LET vDateMax = Floor(MonthEnd(Today()))
6/18/2012 10:37:59 AM: 0082  LET vDateToday = Num(Today())
6/18/2012 10:37:59 AM: 0084  TempCalendar: 
6/18/2012 10:37:59 AM: 0085  LOAD
6/18/2012 10:37:59 AM: 0086  39814 + RowNo() - 1 AS DateNumber, 
6/18/2012 10:37:59 AM: 0087  Date(39814 + RowNo() - 1) AS TempDate 
6/18/2012 10:37:59 AM: 0088  AUTOGENERATE 1 
6/18/2012 10:37:59 AM: 0089  WHILE 39814+IterNo()-1<= 41090
6/18/2012 10:37:59 AM:        2 fields found: TempCalendar.DateNumber, TempCalendar.TempDate, 1,277 lines fetched
6/18/2012 10:37:59 AM: 0091  Calendar: 
6/18/2012 10:37:59 AM: 0092  LOAD
6/18/2012 10:37:59 AM: 0093  Date(TempDate) AS status_date, 
6/18/2012 10:37:59 AM: 0094 
6/18/2012 10:37:59 AM: 0095 
6/18/2012 10:37:59 AM: 0096  Day(TempDate) AS CalendarDayOfMonth, 
6/18/2012 10:37:59 AM: 0097  WeekDay(TempDate) AS CalendarDayName, 
6/18/2012 10:37:59 AM: 0098  Week(TempDate) AS CalendarWeekOfYear, 
6/18/2012 10:37:59 AM: 0099  Month(TempDate) AS CalendarMonthName, 
6/18/2012 10:37:59 AM: 0100  'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 
6/18/2012 10:37:59 AM: 0101  Year(TempDate) AS CalendarYear, 
6/18/2012 10:37:59 AM: 0102 
6/18/2012 10:37:59 AM: 0103 
6/18/2012 10:37:59 AM: 0104  WeekName(TempDate) as CalendarWeekNumberAndYear, 
6/18/2012 10:37:59 AM: 0105  MonthName(TempDate) as CalendarMonthAndYear, 
6/18/2012 10:37:59 AM: 0106  QuarterName(TempDate) as CalendarQuarterMonthsAndYear, 
6/18/2012 10:37:59 AM: 0107 
6/18/2012 10:37:59 AM: 0108 
6/18/2012 10:37:59 AM: 0109  DayStart(TempDate) as CalendarDayStart, 
6/18/2012 10:37:59 AM: 0110  WeekStart(TempDate) as CalendarWeekStart, 
6/18/2012 10:37:59 AM: 0111  MonthStart(TempDate) as CalendarMonthStart, 
6/18/2012 10:37:59 AM: 0112  QuarterStart(TempDate) as CalendarQuarterStart, 
6/18/2012 10:37:59 AM: 0113  YearStart(TempDate) as CalendarYearStart, 
6/18/2012 10:37:59 AM: 0114 
6/18/2012 10:37:59 AM: 0115 
6/18/2012 10:37:59 AM: 0116  DayEnd(TempDate) as CalendarDayEnd, 
6/18/2012 10:37:59 AM: 0117  WeekEnd(TempDate) as CalendarWeekEnd, 
6/18/2012 10:37:59 AM: 0118  MonthEnd(TempDate) as CalendarMonthEnd, 
6/18/2012 10:37:59 AM: 0119  QuarterEnd(TempDate) as CalendarQuarterEnd, 
6/18/2012 10:37:59 AM: 0120  YearEnd(TempDate) as CalendarYearEnd, 
6/18/2012 10:37:59 AM: 0121 
6/18/2012 10:37:59 AM: 0122 
6/18/2012 10:37:59 AM: 0123  'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, 
6/18/2012 10:37:59 AM: 0124  Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 
6/18/2012 10:37:59 AM: 0125  'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays,
6/18/2012 10:37:59 AM: 0126 
6/18/2012 10:37:59 AM: 0127 
6/18/2012 10:37:59 AM: 0128  YeartoDate(TempDate, 0, 1, 41078)*-1 AS CalendarCurrentYTD,
6/18/2012 10:37:59 AM: 0129  YeartoDate(TempDate,-1, 1, 41078)*-1 AS CalendarLastYTD,
6/18/2012 10:37:59 AM: 0130 
6/18/2012 10:37:59 AM: 0131      year(status_date) as StatusDate_YEAR,
6/18/2012 10:37:59 AM: 0132      month(status_date) as StatusDate_MONTH,
6/18/2012 10:37:59 AM: 0133      year(status_date) & '-' & month(status_date) as StatusDate_YearMonth,
6/18/2012 10:37:59 AM: 0134      'Q' & ceil(Month(status_date)/3) as Quarter
6/18/2012 10:37:59 AM: 0135     
6/18/2012 10:37:59 AM: 0136  RESIDENT TempCalendar ORDER BY TempDate ASC
6/18/2012 10:37:59 AM:       Error: Field not found
6/18/2012 10:38:01 AM: 0138  DROP TABLE TempCalendar
6/18/2012 10:38:01 AM:      Execution finished.

[Anonymous · Accepted solution]

Because you used a QUALIFY * statement up in line 56 and never undid it with an UNQUALIFY *, all your subsequent tables are qualified.  So when the script looked for field TempDate, it did not exist.  Only TempCalendar.TempDate exists.

[#5]

Thank you. Now it runs. Some of the charts got messed up, could that be due to unqualifying everything? I guess I'll have to figure out how to fix those.

[#6]

I have a table that is on a different sheet which brings together much of the data that I got from the above script. When I select a date in the calendar, the associated data comes up correct in the tables above, but the new one goes blank anytime I select a date.

Any idea why that could be?