I'm trying to create a calendar based on a table extracted from our Microsoft SQL Server Support Database (the "Incident_Management" table. The first LOAD statement (ClosedDateRange) works fine and retrtieves a single row with the numeric value of the first and last Closed dates for all our Support Calls, however the LET statements immediately following do not retrieve any values causing all the subsequent LOADs to fail.
Has anyone seen this where a variable assignment does not retrieve a value even though there is only a single row in the resident tables with data in each field?
Any pointers greatly appreciated.
Kind Regards,
Ian
The calendar tab in my script looks as follows:
ClosedDateRange: LOAD min(ClosedDate) AS FirstClosedDate, max(ClosedDate) AS LastClosedDate RESIDENT Incident_Management;
LET vn_StartDate = peek('FirstClosedDate',-1,'ClosedDateRange')-1; LET vn_EndDate = peek('LastClosedDate',-1,'ClosedDateRange'); LET vn_DateRange = $(vn_EndDate) - $(vn_StartDate);
//Drop table ClosedDateRange;
QUALIFY *;
CallsClosedTempCalendar: LOAD $(vn_StartDate)+recno() as TempDate autogenerate $(vn_DateRange);
UNQUALIFY ClosedDate;
CallsClosedCalendar: LOAD CallsClosedTempCalendar.TempDate AS ClosedDate, Week(CallsClosedTempCalendar.TempDate) AS Week, Year(CallsClosedTempCalendar.TempDate) AS Year, Month(CallsClosedTempCalendar.TempDate) AS Month, Day(CallsClosedTempCalendar.TempDate) AS Day, Weekday(CallsClosedTempCalendar.TempDate) AS WeekDay, 'Q' & Ceil(Month(CallsClosedTempCalendar.TempDate) / 3) AS Quarter, Date(MonthStart(CallsClosedTempCalendar.TempDate), 'MMM-YYYY') AS MonthYear, Week(CallsClosedTempCalendar.TempDate) & '-' & Year(CallsClosedTempCalendar.TempDate) AS WeekYear RESIDENT CallsClosedTempCalendar ORDER BY CallsClosedTempCalendar.TempDate ASC;
//STORE * FROM CallsClosedCalendar INTO eSD_Calls_Closed_Calendar.qvd;
According to the example I was referring to from the SiB workshop I don't need the "while" because $(vn_DateRange) is supposed to tell autogenerate how many dates to create. $(vn_DateRange) would be calculated from $(vn_EndDate) - $(vn_StartDate).
My problem is that the LET statements aren't retrieving any values from the ClosedDateRange table. The was the example I was given on the workshop course:
// Dynamic Date range is built from data field - Date registered - change as required // Replace all instances of MyDate with the key date field // Replace instance of MyTable with the key date field resident table name // All generated date fields will be prefaced with Cal_ Range: LOAD min(Date) as startdate, max(Date) as enddate resident InvoiceHeader; //Peek out the values for later use let vStart = peek('startdate',-1,'Range')-1; let vEnd = peek('enddate',-1,'Range'); let vRange = $(vEnd) - $(vStart); //Remove Range table as no longer needed Drop table Range; //Generate a table with a row per date between the range above Date: Load $(vStart)+recno() as Date autogenerate $(vRange); //Calculate the Parts you need to examine Calendar: load Date as Date, // date(Date,'dd/mm/yyyy') as Cal_FullDate, Year(Date) as CalendarYear, 'Q'&ceil(Month(Date)/3) AS Cal_Quarter, // right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear, // if(InYear (Date, today(), -1),1) as Cal_FULL_LY, // All Dates Last Year // if(InYear (Date, today(), 0),1) as Cal_FULL_TY, // All Dates This Year if(InYearToDate (Date, today(), 0),1,0) as Cal_YTD_TY, // All Dates to Date this Year if(InYearToDate (Date, today(), -1),1,0) as Cal_YTD_LY, // All Dates to Date Last Year
if(InQuarterToDate (Date, today(), 0),1,0) as Cal_QTR_TQ, if(InQuarterToDate (Date, today(), -1),1,0) as Cal_QTR_LQ,
if(InMonthToDate(Date, today(), 0),1,0) as Cal_MNTH_TM, if(InMonthToDate(Date, today(), -1),1,0) as Cal_MNTH_LM,
// YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY) quartername(Date) as Cal_CalendarQuarter, // quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter, // Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal! Month(Date)&'-'&right(year(Date),4) as Cal_MonthYear, Month(Date) as Cal_Month, Day(Date) as Cal_Day, Week(Date) as Cal_Week, Weekday(Date) as Cal_WeekDay resident Date; //Tidy up Drop table Date;
In your script, you're always peeking at the -1th record. I would expect that in a table containing only 1 row that it shouldn't matter if you used 0, or -1 as the first record (0) is the last (-1). However, I've been surprised by a couple of QlikView oddities, so in this case, I would suggest trying to peek() at the first record (0)
I have to do the same thing in several of my load scripts.
If this doesn't work, what happens when you step through? are these variables being assigned any value at all? or do they remain 'null'?
Many thanks for your reply. I have already tried using 0 in the peek statements but they still return NULL. When stepping through in the debugger, the following messages are generated:
ScriptErrorCount 2 ScriptErrorDetails "Table not found" ScriptErrorList General Error Table Not Found ScriptError Table Not Found autogenerate <NULL> vn_DateRange <NULL> vn_StartDate <NULL> - <NULL> vn_EndDate <NULL> DayNames "Mon;Tue;Wed;Thu;Fri;Sat;Sun" MonthNames "Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec" TimestampFormat "DD/MM/YYYY hh:mm:ss[.fff]" DateFormat "DD/MM/YYYY" TimeFormat "hh:mm:ss" MoneyFormat "£#,##0.00;-£#,##0.00" MoneyDecimalSep "." MoneyThousandSep "," DecimalSep "." ThousandSep ","
It is almost as if the peek function is not working at all. I'm very confused.
Before I started trying this approach to building the calendar, I tried to get the Start and End dates by peek-ing directly into the Incident_Management table. Start Date was retrieved successfully but the End Date was always NULL unless I changed -1 to -2 or something else. Of course this didn't help because the End Date retrieved was not the last date in the table.
I went through my code, and my notes, and I've found what was giving me grief when I tried to use the peek function. I'm not sure it's applicable though.
In my load script, I was loading a field from a SQL database called 'currentTime'; for some reason, I could only reference it if I used all caps (ie: CURRENTIME) in the peek function. I think it had to do with the fact that I didn't use a full load statement, and failed to alias the field. I'd applied the alias in the SQL statement instead.
As a somewhat radical suggestion, try changing your alias names, and the reference in the peek function to all caps... see if that does anything for you.
Here's another code sample, again I use all caps. The other notable difference is that the table's name isn't in quotes. (thogh both of these sets of code work); With regard to the second part of the issue, (peeking at the last record didn't give you the max() date) just sort the table based on your date field first, and you should be fine. This example works with a sorted table.
//Look into the cases object, and find out the min and max dates in it LET varMinDate = num(peek('ORDER_DATE',0,Transactions)); LET varMaxDate = num(peek('ORDER_DATE',-1,Transactions)); LET varToday = num(today()); TempCalendar: LOAD $(varMinDate) + rowno() - 1 AS Num, date($(varMinDate) + rowno() - 1) AS TDATE AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;
For some reason, in all cases, I'm explicitly casting the results from the peek function, eitehr to a date, or to a number. I don't know if that is relevant, but it's interesting.
