SQL and QlikView function

[josephinetedesc]

Hi all

I have a problem - how do i get the QlikView loading Script to only bring into Qlikview the selections

where

the date is < (today + 31 days)

The minimum date is ok cos I have specified in SQL that it begins on April 1st.

The other bit of the problem is that the date comes in as (see below) but I can change it in Qlikview so that it display correctly.  Obviously dirty data which somebody will clean up some time!

35:00.0

00:00.0

00:00.0

20:00.0

10:00.0

25:00.0

19/12/2178

1/01/2016

8/01/2016

20/08/2173

23/07/3006

16/03/2176

At one stage I wrote:

SQL

SELECT distinct *
FROM ZZ_Utilisation_trial

where My date < 00:00.0

;

but that was not accepted ...

Also how come 1/1/2016 is the same as 00:00.0???

Thank you

Jo

[Peter_Cammaert]

Add a PRECEDING LOAD to the SELECT statement, which looks a bit like:

LOAD * WHERE [My Date] < (today() + 31);

SELECT ...

Peter

[josephinetedesc]

SQL

SELECT distinct *
FROM ZZ_Utilisation_trial
Where [App_DtTm] < (today() + 31);

This was the message received:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'today' is not a recognized built-in function name.

SQL

SELECT distinct *

FROM ZZ_Utilisation_trial

Where [App_DtTm] < (today() + 31)

;

[rbecher]

Hi Josephine,

could you post the complete part of the script including LOAD and SQL. I guess there is something wrong with the WHERE parameter.

I would not use the WHERE clause in preceding LOAD because then you will receive the whole table data from database and filter on QlikView side which could take a huge time..

- Ralf

[PrashantSangle]

Hi,

You have clean you date field record either while loading data through SQL

or Use suggestion given by Peter there also you need to clear you data then compare with date

like

Date(Date#([My Date],'DD/MM/YYYY'))< Date(Today() +31)

or

Num(Date#([My Date],'DD/MM/YYYY'))< Num(Today() +31)

Regards

[rbecher]

Just try:

SELECT distinct *

FROM ZZ_Utilisation_trial

Where [App_DtTm] < (CONVERT(date, getdate()) + 31)

[PrashantSangle]

Hi,

Today() is QlikView Function not SQL

in SQL you have sysdate()

Regards

[Peter_Cammaert]

Just a bit of advise:

The whole SELECT part is sent to the RDBMS through OLEDB for execution. today() is not a SQL Server function so you will get an error message about that. A PRECEDING LOAD adds a QlikView LOAD statement that is executed by the QV Engine (not SQL engine) immediately after the SELECT and using the data returned by the SELECT statement. today() is a regular QlikView statement, so it will work as expected.

Do NOT add my WHERE clause to your SELECT statemenbt because SQL Server won't understand what you mean.

Best,

Peter

[rbecher]

..rather add rhe right SQL-Where clause to your SQL statement to filter the data on the database side.

Use preceding LOAD only to transform data, not to filter!

- Ralf