topic PEEK & SQL in QlikView

PEEK & SQL

Wed, 24 Jun 2009 23:08:19 GMT

Hi,

Can anyone point out my silly mistake in the code bellow it returns a NULL value each time, or is it not possible to use peek on a SQL loaded table, it seems to work fine if I load from a QVD. I assumed that once the record was loaded it would respond in the same way ...


Para1:
LOAD "curr-year" AS [Current Year],
 "last-year" AS [Last Year],
 "next-year" AS [Next Year],
 "period-no" AS [Period],
 "report-pd" AS [Report Period],
 "report-wk" AS [Report Week],
 "week" AS [Week];
SQL SELECT "curr-year", "last-year", "next-year", "period-no", "report-pd",
 "report-wk", "week"
FROM PUB.para1;
LET vCurrentYear = year(peek('[Current Year]',1,'Para1'));

PEEK & SQL

Anonymous — Wed, 24 Jun 2009 23:16:57 GMT

You're using peek outside of SQL, so this part is fine. Table name here is not needed since you're using LET immideately after the Paral table. Try to remove brackets. And, if Current Year field is not in a date format, you don't need year() function:
LET vCurrentYear = peek('Current Year',1);

PEEK & SQL

Oleg_Troyansky — Wed, 24 Jun 2009 23:21:04 GMT

In addition to Michael's suggestions:

1. check that you are actually getting data from your SQL statement and what are the field names.

2. Check that you don't have a previous load (from QVD, for example), that's loading the same fields into a different table name. Maybe your two tables are being automatically concatenated and this table name simply doesn't exist).

Oleg

PEEK & SQL

Thu, 25 Jun 2009 17:21:23 GMT

Thanks both for your reply, it turns out that the problem was the square brackets round the field name. My mistake, I was under the impression that this was used every time a field name contained a space.

PEEK & SQL

Anonymous — Thu, 25 Jun 2009 23:24:14 GMT

Filed name here is a string in single quotes, so space doesn't matter. Brackets were assumed to be a part of the field name, I think.