topic AW:partiall Select with where condition in QlikView

partiall Select with where condition

Wed, 16 Jun 2010 13:50:05 GMT

Hello,

I wanted to load data via SQL-Select from an ODBC Database. The QlikView file already load all Data from the Database (this takes a very long time). And now I want to update the Data to the newest Version. There is an timstamp in the Table which I wanted to use like this:

SQL Select
...
FROM ...

where timestamp >= old timestamp which is already loaded in the file.

But theres an error: oldtimstamp is invalid in the used context

best regards
Ingo Placzek

AW:partiall Select with where condition

biester — Wed, 16 Jun 2010 13:54:34 GMT

Try with a variable:

let oldtimestamp = .....

SQL select ....
FROM .....
where timestamp >= $(oldtimestamp) ....

Rgds,
Joachim

AW:partiall Select with where condition

Wed, 16 Jun 2010 14:02:52 GMT

hi, then he told me:

Invalid use of a column or OLAP function

AW:Re: AW:partiall Select with where condition

biester — Wed, 16 Jun 2010 14:06:44 GMT

Would be helpful if you posted the script.

AW:Re: AW:partiall Select with where condition

Wed, 16 Jun 2010 14:13:03 GMT

add

Max

(Status.LASTOCCURRENCE) as oldLASTOCCURRENCE

resident

Status;

Status:

add

Load

week

(FIRSTOCCURRENCE) as Woche,

year

(FIRSTOCCURRENCE) as Jahr,

month

(FIRSTOCCURRENCE) as Monat,

day

(FIRSTOCCURRENCE) as Tag,

hour

(FIRSTOCCURRENCE) as Stunde,

minute

(FIRSTOCCURRENCE) as Minute;

Status:

SQL

SELECT

ACKNOWLEDGED,

ADVCORRCAUSETYPE,

ADVCORRSERVERNAME,

ADVCORRSERVERSERIAL,

AGENT,

ALERTGROUP,

ALERTKEY,

CAUSETYPE,

CLASS,

CORRSCORE,

CUSTOMER,

DELETEDAT,

DUPSERIAL,

DVAGENTOS,

DVAPPHBMAXAGE,

DVEVENTSTOESCAL,

DVEVTSTOESCAL,

DVITMAGENTMONSOURCE,

DVITMAGENTPOLICYID,

DVITMAGENTVER,

DVKLSTATUS,

EVENTID,

EXPIRETIME,

EXTENDEDATTR,

FIRSTOCCURRENCE,

FLASH,

FQMANAGER,

GRADE,

IDENTIFIER,

LASTMODIFIED,

LASTOCCURRENCE,

LOCALNODEALIAS,

LOCALOBJRELATE,

LOCALPRIOBJ,

LOCALROOTOBJ,

LOCALSECOBJ,

LOCALTERTOBJ,

LOCATION,

LOWESCL,

MANAGER,

NMOSCAUSETYPE,

NMOSDOMAINNAME,

NMOSENTITYID,

NMOSMANAGEDSTATUS,

NMOSOBJINST,

NMOSSERIAL,

NODE,

NODEALIAS,

ORIGINALSEVERITY,

OWNERGID,

OWNERUID,

PHYSICALCARD,

PHYSICALPORT,

PHYSICALSLOT,

POLL,

PROCESSREQ,

REMOTENODEALIAS,

REMOTEOBJRELATE,

REMOTEPRIOBJ,

REMOTEROOTOBJ,

REMOTESECOBJ,

REMOTETERTOBJ,

SERIAL,

SERVERNAME,

SERVERSERIAL,

SERVICE,

SEVERITY,

STATECHANGE,

SUMMARY,

SUPPRESSESCL,

TALLY,

TASKLIST,

"TYPE",

URL,

X733CORRNOTIF,

X733EVENTTYPE,

X733PROBABLECAUSE,

X733SPECIFICPROB

FROM

TG01885."REPORTER_STATUS"

where

FIRSTOCCURRENCE >= $(OLDLASTOCCURRENCE)

;

load

AW:Re: AW:partiall Select with where condition

Wed, 16 Jun 2010 14:16:47 GMT

this looks very bad. i copyed it out from qlikview

AW:Re: AW:Re: AW:partiall Select with where condition

biester — Wed, 16 Jun 2010 14:32:22 GMT

Don't mix up columns and variables.

An example:

TABLE:
SQL SELECT MAX(DATUM) as COLUMN_MAXDAT from SQL_TABLE;

let VARIABLE_MAXDAT = peek('COLUMN_MAXDAT',0,'TABLE');

DATA:
SQL SELECT DATUM,RG_NR from SQL_TABLE where DATUM < '$(VARIABLE_MAXDAT)';

Rgds,
Joachim