topic Re: Load Script - Date Issue in QlikView

Load Script - Date Issue

laujerry — Sat, 12 Oct 2013 16:52:03 GMT

Hi all, please advise what's wrong with the following script, thanks!

LET vYesterday=MakeDate(2013,10,12);

[VBAK]:

LOAD

VBELN,KUNNR,BSTNK,

VKGRP,VKORG,VTWEG,

SPART,AUDAT,VDATU,

AUART,WAERK,KNUMV,

FAKSK,ERNAM,VKBUR,

KTEXT,ERDAT,ERZET;

SQL Select

VBELN KUNNR BSTNK

VKGRP VKORG VTWEG

SPART AUDAT VDATU

AUART WAERK KNUMV

FAKSK ERNAM VKBUR

KTEXT ERDAT ERZET

from VBAK WHERE

Date(ERDAT,'dd/MM/yyyy')='$(vYesterday)';

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Comma without preceding colon (after OPEN ?).)

Re: Load Script - Date Issue

swuehl — Sun, 13 Oct 2013 21:19:31 GMT

The SQL WHERE clause is sent to the DB driver as is (after doing the dollar sign expansion only), and your driver probably doesn't know the QV Date() function.

Either use a function that is supported by the driver, or compare the field directly with a properly formatted variable.

Re: Load Script - Date Issue

Tue, 15 Oct 2013 13:14:30 GMT

Hello,

Do it like this :

let vYesterday = date(Today()-1,'YYYYMMDD'); // Dates in SAP are like this

trace >> $(vYesterday); //

...

from VBAK WHERE

ERDAT ='$(vYesterday)';

Re: Load Script - Date Issue

laujerry — Wed, 16 Oct 2013 01:49:59 GMT

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 "'20131015'" is not a valid comparison operator.)

It still reports error

Re: Load Script - Date Issue

sudeepkm — Wed, 16 Oct 2013 15:41:20 GMT

you may also have to use date variable in your script like below:

$(#vYesterday) or '$(#vYesterday)'

A good article on Date fields by Henric at here: QlikView Date fields