topic Re: Error: Garbage after statement in QlikView

Error: Garbage after statement

Fri, 30 May 2014 20:56:13 GMT

I have a document with an incremental load. I'm reading in a QVD and purging my data for anything older than three months. I then want to concatenate that data set to the current day's transactions. For some reason I'm getting a rather unusual error after the statement and I can't figure out why. I will say in the log file it shows shows the STORE TSRTRAN command executing successfully, but my QVD doesn't get updated and I get an error. I've attached both the qvw and log file. I appreciate any insight!

Matt

MaxDate:

LOAD

max(CRT_DT) as LastCRT_DT

FROM $(Lvl1QVDdirectory)TSRTRAN.qvd (qvd);

vLastLoadDate = chr(39) & date(peek('LastCRT_DT',-1,'MaxDate'),'YYYY-MM-DD') & chr(39);

DROP Table MaxDate;

vDailyLoadDate = chr(39) & date(Today(),'YYYY-MM-DD') & chr(39);

//Gather current run's dataset which will be appeneded to the master

//QVD.

TSRTRAN_tmp:

LOAD TRAN_ID,

PRNT_TRAN_ID,

SRV_ID,

DOC_ID,

CRT_TS,

MOD_TS,

ERR_TYP_ID,

PHYS_SRVR_ID,

STTS_ID,

CUR_RETRIES_NUM,

USR_ID,

END_TS,

BUS_STTS,

CRT_DT

FROM $(Lvl1QVDdirectory)TSRTRAN.qvd (qvd)

where CRT_DT > $(vQVDPurgeDate);

Concatenate

SQL SELECT "TRAN_ID",

"PRNT_TRAN_ID",

"SRV_ID",

"DOC_ID",

"CRT_TS",

"MOD_TS",

"ERR_TYP_ID",

"PHYS_SRVR_ID",

"STTS_ID",

"CUR_RETRIES_NUM",

"USR_ID",

"END_TS",

"BUS_STTS",

"CRT_DT"

FROM WMBTCH.TSRTRAN

where CRT_DT > $(vLastLoadDate) and CRT_DT < $(vDailyLoadDate);

STORE TSRTRAN_tmp into $(Lvl1QVDdirectory)TSRTRAN.qvd(qvd);

DROP Table TSRTRAN;

Re: Error: Garbage after statement

maxgro — Fri, 30 May 2014 21:05:14 GMT

maybe

in first load

5/30/2014 4:42:28 PM: 0103 CRT_DT

5/30/2014 4:42:28 PM: 0104 FROM ..\QVD\Extract\TSRTRAN.qvd (qvd)

5/30/2014 4:42:28 PM: 0105 where CRT_DT > ';

vQVDPurgeDate is not defined

Re: Error: Garbage after statement

Fri, 30 May 2014 21:07:12 GMT

Thanks for the reply Massimo. I have it set on my main tab

LET vQVDPurgeDate = chr(39) & today()-92 & chr(39);

It works when I run just that snippet. Something odd is going on here.

Re: Error: Garbage after statement

Fri, 30 May 2014 21:07:41 GMT

But your right it doesn't show up in the log.

Re: Error: Garbage after statement

Anonymous — Fri, 30 May 2014 21:13:39 GMT

When using date variables like this, I always put them in single quotes:

where CRT_DT > '$(vLastLoadDate)' and CRT_DT < '$(vDailyLoadDate)';

Re: Error: Garbage after statement

Fri, 30 May 2014 21:16:32 GMT

Thanks Michael - I'll try that out next. This is error stands between me and my vacation so I appreciate all suggestions right now. Do the single quotes change the way QV interprets the variables? I've never used them before, but I'm fairly new.

Re: Error: Garbage after statement

Anonymous — Fri, 30 May 2014 21:20:46 GMT

At least it will interpret the date correctly. For example, If I used a fixed date, it would be

where CRT_DT > '2014-05-30'

never

where CRT_DT > 2014-05-30

Re: Error: Garbage after statement

Anonymous — Fri, 30 May 2014 21:36:26 GMT

Hi, Matthew.

The variable vQVDPurgeDate is not showing in the log because it is really not being set properly. Check the Variables Overview. Try changing the command to:

LET vQVDPurgeDate = chr(39) & date(today()-92,'YYYY-MM-DD') & chr(39);

Edit: You'll probably get an error on the select for the new transaction because vLastLoadDate is being set to YYYY-MM-DD.

Hope it helps.

Regards.

Re: Error: Garbage after statement

Fri, 30 May 2014 21:59:16 GMT

Thank you Bruno!! That was it I need to format it as date....Frustrating, but I hopefully won't make that oversight in the near future. I really appreciate everyone's help!