Using Variable in SQL statement

[#1]

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

[#2]

Hi,

try not using $(MaxTimestamp) but only

where timestamp > MaxTimestamp;

Hope it helps

Giampiero

[jagannalla]

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')'

[#4]

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

[llauses243]

Hi,

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

Good luck, Luis

[jagannalla · Accepted solution]

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)'

[#7]

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

[#8]

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