Calendar help

Mon, 18 Jun 2012 14:08:21 GMT

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!

Re: Calendar help

Anonymous — Mon, 18 Jun 2012 14:25:37 GMT

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.

Re: Calendar help

Mon, 18 Jun 2012 14:40:55 GMT

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.

Re: Calendar help

Anonymous — Mon, 18 Jun 2012 14:56:02 GMT

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.

Re: Calendar help

Mon, 18 Jun 2012 15:21:26 GMT

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.

Re: Calendar help

Tue, 19 Jun 2012 18:50:23 GMT

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?

topic Re: Calendar help in QlikView

Calendar help

Re: Calendar help

Re: Calendar help

Re: Calendar help

Re: Calendar help

Re: Calendar help