topic Using Variable in SQL statement in QlikView

Using Variable in SQL statement

Mon, 14 May 2012 10:12:18 GMT

Hello Everyone,

I have created a variable called MaxTimeStamp like this:

MaxTab:
LOAD

max(timestamp) as MaxStamp

from test.qvd

Let MaxStamp = peek('MaxStamp', 0, MaxTable)

But when we used this variabale $(MaxStamp) in SQL select statement is not working.

LOAD

...

testcolumn;

SQL SELECT *

FROM testtable

where timestamp > $(MaxTimestamp);

Might be there is some problem in syntax or there is a specific way to use variable in SQL select statement.

Waiting for your replies...

BR!

Sammm

Using Variable in SQL statement

Mon, 14 May 2012 10:17:24 GMT

Hi,

try not using $(MaxTimestamp) but only

where timestamp > MaxTimestamp;

Hope it helps

Giampiero

Re: Using Variable in SQL statement

jagannalla — Mon, 14 May 2012 10:22:30 GMT

Hello,

Try this:

where timestamp > '$(MaxTimestamp)'

- But make sure your timeformat should match with sql time format. B'coz to catch max value you are using Max syntax of qlikview, it gives in numer format. So you need to change to timeformat as per sql time stamp. For example if your timeformat of sql is in below format

2011-06-08 00:10:12

you need to use where timestamp > 'Date($(MaxTimestamp),'YYYY-MM-DD hh:mm:ss')'

Using Variable in SQL statement

Mon, 14 May 2012 10:26:04 GMT

Hi Jagan, thanks for reply but its not working, I have tried this already.

Re: Using Variable in SQL statement

llauses243 — Mon, 14 May 2012 10:30:33 GMT

Hi,

A detail ... unequal LET & WHERE, pls to look image attached

Good luck, Luis

Re: Using Variable in SQL statement

jagannalla — Mon, 14 May 2012 10:32:28 GMT

make sure your timeformat should match with sql time format. B'coz to catch max value you are using Max syntax of qlikview, it gives in numer format. So you need to change to timeformat as per sql time stamp. For example if your timeformat of sql is in below format

2011-06-08 00:10:12

you need to use where timestamp > 'Date($(Maxstamp),'YYYY-MM-DD hh:mm:ss')'

other wise change here itself

MaxTab:

LOAD

max(timestamp) as MaxStamp

from test.qvd

Let MaxStamp = peek('MaxStamp', 0, MaxTable);

Let vMaxDate=Date('$(MaxStamp)','YYYY-MM-DD hh:mm:ss');

where timestamp > '$(vMaxDate)'

Using Variable in SQL statement

Mon, 14 May 2012 10:44:03 GMT

hello Luis, Yes its just a typing mistake when i wrote the question here.but its same on my script.

Using Variable in SQL statement

Mon, 14 May 2012 10:54:00 GMT

Thanks Jagan, let me try this one..i think you are right.I will reply after checking this code