topic Help Please: Variable Assignment Not Working? in QlikView

Help Please: Variable Assignment Not Working?

Mon, 28 Jun 2010 15:37:20 GMT

Hello All,

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;

Help Please: Variable Assignment Not Working?

Mon, 28 Jun 2010 15:45:23 GMT

check you fogot While there..

CallsClosedTempCalendar:
LOAD
 $(vn_StartDate)+recno() as TempDate
autogenerate $(vn_DateRange)
while ($(vn_StartDate) + iterno()-1 <=$(vn_StartDate))

Help Please: Variable Assignment Not Working?

Mon, 28 Jun 2010 15:56:05 GMT

Hi sravan,

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;

Help Please: Variable Assignment Not Working?

Tue, 29 Jun 2010 01:00:10 GMT

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'?

Help Please: Variable Assignment Not Working?

Tue, 29 Jun 2010 09:50:46 GMT

Hi Ryan,

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.

Kind Regards,

Ian

Help Please: Variable Assignment Not Working?

Tue, 29 Jun 2010 17:02:11 GMT

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.