topic Re: Re: Date in SELECT statement - QV script in QlikView

Date in SELECT statement - QV script

Mon, 07 Apr 2014 11:18:14 GMT

Hi all,

I'm stuck with a problem.

I want to use the current date (7-4-2014) in my SELECT statement.

Codetracker:

SQL

SELECT

Code,
Count(Code) as aantal,
CurrentDate() as afldatum
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

;

I have searched on the internet to find the solution, but all I can find is how to use date outside QV script, or in the WHERE statement in SQL script.

Anyone know how I can solve this?

Re: Date in SELECT statement - QV script

Mon, 07 Apr 2014 11:34:53 GMT

Hi,

Try this

Load Code,

Today() as afldatum,

Count(Code) as aantal

Group By Code;

SQL SELECT

Code
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

;

Re: Re: Date in SELECT statement - QV script

Mon, 07 Apr 2014 12:04:21 GMT

Ravi,

That's not working.

It gives me an error of incorrect syntax (no FROM after the first LOAD?)

I tried to give only the important part of the statement, but maybe that's confusing. So this is the whole SQL SELECT:
Today() doesn't work in SQL, so how can I put today's date on the 7-4-2014 line?

SQL

Select Zuil,
Code,
Count(Code) as aantal,
'7-4-2014' as afldatum

FROM DATASTORE.dbo."DS_ORDRS"
Where Code not in (0,99)
and right (Zuil,1) in (3,7)
group by Zuil, Code
Order by Zuil, Code
;

PS: This works. But I need a dump every day, so that's why I need a funtion as Today()

Re: Date in SELECT statement - QV script

maxgro — Mon, 07 Apr 2014 12:05:00 GMT

if you want current date (and / or time) in sql please let we know for which database

sql server --> getdate()

oracle --> sysdate

etc....

Re: Re: Date in SELECT statement - QV script

Mon, 07 Apr 2014 12:58:39 GMT

I use the OLE DB Connect in Qlikview.

Re: Date in SELECT statement - QV script

qvwarning — Mon, 07 Apr 2014 13:02:11 GMT

Hi Kevin,

try preceding load of data with 'where' in 2. load

like:

Load

Date(F1_Date) as DATE_NEW,

Where Date(F1_Date)='07.04.2014';

Load * Inline

[F1_Date, F2

01.01.2014,10

02.01.2014,20

07.04.2014,70];

Re: Re: Date in SELECT statement - QV script

Mon, 07 Apr 2014 13:38:53 GMT

Hi Andreas,

I just want to use the current date of today.

I'm loading from my DB and I want to add a column with the current date.

This script will load every day and will be stored into a QVD file with use of the CONCATENATE function.

Re: Date in SELECT statement - QV script

jerrysvensson — Mon, 07 Apr 2014 14:03:19 GMT

Ravis code works.

"Today() doesn't work in SQL, so how can I put today's date on the 7-4-2014 line?"

Look at his example again:

Today() is called in the LOAD section, NOT in the SQL SELECT section.

Table:

Load Code,

Today() as afldatum,

Count(Code) as aantal

Group By Code;

SQL SELECT

Code
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

Re: Re: Re: Date in SELECT statement - QV script

qvwarning — Mon, 07 Apr 2014 14:04:28 GMT

Hi Kevin,

the script below get values from today only.

you get the dimension DATE_Today with current date.

then load the qvd und concatenate with preceding load [ValueToday]

regards

Andreas

Re: Re: Date in SELECT statement - QV script

Mon, 07 Apr 2014 14:21:57 GMT

Thanks for all the help guys.

I found a different solution on the community, using a variable in the SQL statement.

So I didn't check the last two solutions.

This is the solution I used.

Let vToday = Today();

SQL
Select Zuil,
Code,
Count(Code) as aantal,
'$(vToday)' as AflDat

FROM DATASTORE.dbo."DS_ORDRS"
Where Code not in (0,99)
and right (Zuil,1) in (3,7)
group by Zuil, Code
Order by Zuil, Code
;