topic Re: SQL and QlikView function in QlikView

SQL and QlikView function

josephinetedesc — Fri, 05 Jun 2015 07:43:01 GMT

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

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

Re: SQL and QlikView function

Peter_Cammaert — Fri, 05 Jun 2015 07:46:31 GMT

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

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

SELECT ...

Peter

Re: SQL and QlikView function

josephinetedesc — Fri, 05 Jun 2015 07:51:36 GMT

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)

;

Re: SQL and QlikView function

rbecher — Fri, 05 Jun 2015 07:52:05 GMT

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

Re: SQL and QlikView function

PrashantSangle — Fri, 05 Jun 2015 07:54:47 GMT

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

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

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

Regards

Re: SQL and QlikView function

rbecher — Fri, 05 Jun 2015 07:55:21 GMT

Just try:

SELECT distinct *

FROM ZZ_Utilisation_trial

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

Re: SQL and QlikView function

PrashantSangle — Fri, 05 Jun 2015 07:55:36 GMT

Hi,

Today() is QlikView Function not SQL

in SQL you have sysdate()

Regards

Re: SQL and QlikView function

Peter_Cammaert — Fri, 05 Jun 2015 08:00:40 GMT

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

Re: SQL and QlikView function

rbecher — Fri, 05 Jun 2015 08:06:52 GMT

..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