Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Date in SELECT statement - QV script

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?

Tags (3)
9 Replies
Not applicable

Re: Date in SELECT statement - QV script

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)

;

Not applicable

Re: Re: Date in SELECT statement - QV script

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

MVP
MVP

Re: Date in SELECT statement - QV script

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

sql server     --> getdate()

oracle          --> sysdate

etc....

Not applicable

Re: Re: Date in SELECT statement - QV script

I use the OLE DB Connect in Qlikview.

qvwarning
Contributor

Re: Date in SELECT statement - QV script

Hi Kevin,

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

like:

Load

    Date(F1_Date) as DATE_NEW,

    F2

Where Date(F1_Date)='07.04.2014';

Load *  Inline

    [F1_Date, F2

    01.01.2014,10

    02.01.2014,20

    07.04.2014,70];

Not applicable

Re: Re: Date in SELECT statement - QV script

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.

jerrysvensson
Valued Contributor II

Re: Date in SELECT statement - QV script

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)

qvwarning
Contributor

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

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

Not applicable

Re: Re: Date in SELECT statement - QV script

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
;

Community Browser