topic Re: Using a variable within SQL select statement in QlikView

Using a variable within SQL select statement

Sun, 12 Jan 2014 23:53:47 GMT

Hi All,

My first foray into incremental loads is not going as planned I have used the following steps and am now somewhat stuck.

1. load previous qvd file

2. Create new table with max date of file

3. Set variable to equal max data from file (using let and peek statement)

all of this works fine...i will drop the temp table once I can get this to work...my problem is getting the SQL Select (via ODBC connection) to recognise the variable within the where statement. I have tried a few different methods (preceding load, Setting the variable, letting the variable, adding quatations, removing quotations, but I cant get the syntax right. Any help would be greatly appreciated

Re: Using a variable within SQL select statement

Sokkorn — Mon, 13 Jan 2014 01:15:53 GMT

Hi Rick,

Here is an idea for load script

[MaxDateTable]:

LOAD Max(LastModify) AS [MaxDate] FROM Transaction.qvd (qvd);

Let vMaxDate = Date(Peek('MaxDate',0,'MaxDateTable'),'YYYY-MM-DD');

//LET vMaxDate = Date(Peek('MaxDate',0,'MaxDateTable'),'DD-MM-YYYY');

//LET vMaxDate =TIMESTAMP(Peek('MaxDate',0,'MaxDateTable'),'YYYY-MM-DD hh:mm:ss.fff');

DROP TABLE [MaxDateTable];

[TableSQL]:

SQL Select * FROM tblName WHERE LastModify > '$(vMaxDate)';

//SQL Select * FROM tblName WHERE LastModify > CHR(39) & $(vMaxDate) & CHR(39);

*** You may test it line by line via "Comment" and "Uncomment"

Can you share your script here?

Regards,

Sokkorn

Re: Using a variable within SQL select statement

Mon, 13 Jan 2014 15:32:48 GMT

Hi Rick , I am suspecting, the problem with variable conversion into your date field datatype and syntax will change based on the DB Type.

If the DB is SQL Server , you don't to convert the variable into date format

WHERE DATE_FIELD >= '$(vMaxDate)'

If the DB is Oracle, the syntax will depend on the DATE_FIELD data type.

1. If the DATE_FEILD data type is DATE then,

LET vMaxDate = Date(Peek('MAX_DATE') , 'YYYY-MM-DD') ;

SELECT * FROM TABLE WHERE DATE_FIELD >= TO_DATE('$(vMaxDate)' , 'YYYY-MM-DD') ;

2. If the DATE_FEILD data type is TIMESTAMP then,

LET vMaxDate = Date(Peek('MAX_DATE') , 'YYYY-MM-DD hh:mm:ss') ;

SELECT * FROM TABLE WHERE DATE_FIELD >= TO_DATE('$(vMaxDate)' , 'YYYY-MM-DD HH24:MM:SS') ;

Note: First test your SQL Where cond query outside qlikview like TOAD or any tool then use in Qlikview.

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 00:50:20 GMT

Thank you Sokkorn and Dathu for your helpful tips.

I can confirm that the SQL select statement works as intended in HeidiSQL, however when running via QV, it is erring as though [where Date > '$(vMaxNPS)';] is blank (returns syntax equivalent to [where Date > '']

I get the feeling that the problem may lie in the variabel itself. When I call the field name in a text box (MaxNPSDate) i get the 5 digit value...but when i can the variable $(vMaxNPS) I get nothing.

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 00:50:38 GMT

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 00:50:46 GMT

Re: Using a variable within SQL select statement

Sokkorn — Tue, 14 Jan 2014 01:04:41 GMT

Hi Rick,

Can you share your script. So that we can find it out together; spot on why vMaxNPS return nothing.

Regards,

Sokkorn

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 01:13:56 GMT

Hi Sokkorn

I've had to remove field names due to sensitivity, but here is the script. I also seem to have another problem in storing the newly acquired data into a separate qvd.

//Load previos qvd file

NPS:

LOAD [8 Data Fields]

FILE_DATE,

[5 Fact Fields]

FROM

nps.qvd

(qvd);

//Set max data date for file in sql format. This returns '2014-06-01' which is acceptable for MySQL however when calling the variable returns blank.

MaxNPSTable:

LOAD

Date(max(FILE_DATE),'YYYY-MM-DD') as MaxNPSDate

FROM NPS.qvd(qvd);

//set variable to the max data date

LET vMaxNPS = Date(Peek(MaxNPSDate,0,MaxNPSTable),'YYYY-MM-DD');

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

[5 fact fields],

[8 data Fields]

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';

Store NPSNEW into NPSNEW.qvd(qvd);

Re: Using a variable within SQL select statement

Sokkorn — Tue, 14 Jan 2014 01:19:56 GMT

Hi Rick,

Can you try this one

[MaxNPSTable]:

LOAD

Max(FILE_DATE) AS [MaxNPSDate]

FROM NPS.qvd (qvd);

//set variable to the max data date

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

Edit: Changed log

1. Changed Date(max(FILE_DATE),'YYYY-MM-DD') to Max(FILE_DATE)

2. Changed LET vMaxNPS = Date(Peek(MaxNPSDate,0,MaxNPSTable),'YYYY-MM-DD'); to

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

Regards,

Sokkorn

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 05:13:48 GMT

Thanks Sokkorn

I aplpied your changes and it has given much the same result. MaxNPSDate is equal to the excel equivalent of 6th january (41645) however when calling the variable within a text object to test it the result is 2007. (within the text object is =$(vMaxNPS)

also, when running the SQL extract it retrieves the entire table and appends it to the existing dataset within the QVW. I will try some more tomorrow...this one will not beat me!!

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 14:10:54 GMT

As per your comments, the variable didn't have the date field,

Use the TRACE command to show the value of variable in the load script and also check the log file after fail, then you can see which value holding in the variable.

TRACE Max Date value ----> $(vMaxDate);

Can you please post your Variable definition in LET statement ?

Re: Using a variable within SQL select statement

Tue, 14 Jan 2014 23:38:15 GMT

Hi Dathu

I have tried a couple of variations for the LET statement

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD'); returns 2007 for some reason

LET vMaxNPS = Date(Peek(MaxNPSDate,0,MaxNPSTable),'YYYY-MM-DD'); returns -

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 01:17:41 GMT

TRACE of the vMaxNPS shows blank

and when I debug, the LET statement appears to read the field MaxNPSDate from the MaxNPSTable as NULL

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 03:51:56 GMT

Can you please post whole script what are you using.

I am suspecting the problem with MaxNPSTable and how many values exist in this table?

This table must have only one value and that would be maximum date value. Please dont drop the table and check what is the value on the table ? In the this table having only one date value then there shouldn't be a problem.

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 04:04:39 GMT

Hi Dathu

The table only contains a single value

Here is the complete script

NPS:

LOAD

FILE_DATE,

FROM

nps.qvd

(qvd);

MaxNPSTable:

LOAD

Max(FILE_DATE) AS

FROM NPS.qvd(qvd);

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

FROM dashboarddata.nps

where Date > $(vMaxNPS);

Rick

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 04:20:21 GMT

Try like below:

NPS:

LOAD

FILE_DATE,

FROM nps.qvd (qvd);

MaxNPSTable:

LOAD Max(FILE_DATE) AS MaxNPSDate

FROM NPS.qvd(qvd);

LET vMaxNPS = Date(Peek('MaxNPSDate'),'YYYY-MM-DD');

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';

if not work, what is value you are getting in the MaxNPSTable (date or string) ?

Re: Using a variable within SQL select statement

Sokkorn — Wed, 15 Jan 2014 06:33:15 GMT

Hi Rick,

Please load two statement below only and give me the result:

MaxNPSTable:

LOAD Max(FILE_DATE) AS [MaxNPSDate] FROM NPS.qvd (qvd);

// What is the value of field [MaxNPSDate]?

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

// What is the value of variable vMaxNPS?

Awaiting your prompt response.

Regards,

Sokkorn

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 06:43:39 GMT

Hi Sokkorn

MaxNPSTable:

LOAD Max(FILE_DATE) AS FROM NPS.qvd (qvd);

Returns a value of 41645 (excel equivalent of 6th Jan 2014)

LET vMaxNPS = Date(Peek('MaxNPSDate',0,'MaxNPSTable'),'YYYY-MM-DD');

When specified as =vMaxNPS shows 2014-01-06 but when specified as =$(vMaxNPS) shows 2007

Rick

Re: Using a variable within SQL select statement

Sokkorn — Wed, 15 Jan 2014 10:15:54 GMT

Hi Rick,

Here is my test

Result after load

You see the line that I use TRACE function with $(vMaxNPS), then it return 2014-01-06. Not sure why you got 2007

Regards,

Sokkorn

Re: Using a variable within SQL select statement

Wed, 15 Jan 2014 13:50:30 GMT

Hi Rick, can you check what is your SET DateFormat variable ?

Try Like below:

LET vMaxNPS = text(Date(Peek('MaxNPSDate'),'YYYY-MM-DD')) ;

ODBC CONNECT TO SDCC;

NPSNEW:

SQL SELECT

Date as 'FILE_DATE',

FROM dashboarddata.nps

where Date > '$(vMaxNPS)';