topic Re: SQL Load in QlikView

SQL Load

danielnevitt — Mon, 21 Oct 2013 13:52:40 GMT

Hi,

I'm trying to write a SQL load statement to restrict my data by TIMEMODIFIED greater than or equal to today.

My first issue is that my load statement (see below) doesn't seem to work and I have no idea why?

//-------- Start Multiple Select Statements ------

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE TIMEMODIFIED = 21/10/2013;
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

//-------- End Multiple Select Statements ------

Secondly is it possible to have the date (in this case 21/10/2013) as a variable that equals today?

Any help will be really appreciated.

Thanks,

Daniel

Re: SQL Load

javier_florian — Mon, 21 Oct 2013 13:56:34 GMT

Restrict in SQL DB:

Table_A:
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE

WHERE CONVERT(DATETIME, DATA_FIELD) <= CONVERT(DATETIME, 'DD/MM/YYYY');

Re: SQL Load

Anonymous — Mon, 21 Oct 2013 14:06:15 GMT

Daniel, you can try this:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED' = '21/10/2013'; // notice single quites
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

For today:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED) = num(today());
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

Reagrds,

Michael

Re: SQL Load

MayilVahanan — Mon, 21 Oct 2013 14:19:34 GMT

Try like this

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE Date(TIMEMODIFIED,'DD/MM/YYYY') >= MakeDate(2013,10,21);;

Re: SQL Load

danielnevitt — Mon, 21 Oct 2013 15:27:44 GMT

Hi Michael,

Thank you for the reply. Unfortunately when I try to run that script I receive the following message:

ODBC read failed

SQL SELECT *

FROM "IXOMEX_OWNER".ZZQUOTEPRICE

Do you have any idea what the issue might be?

Thanks,

Daniel

Re: SQL Load

javier_florian — Mon, 21 Oct 2013 15:30:49 GMT

Do you check you odbc connection? And do you have odbc line in your script?

Re: SQL Load

danielnevitt — Mon, 21 Oct 2013 15:35:54 GMT

Hi Javier,

The ODBC connection is working ok (I checked without the LOAD and WHERE statement). The ODBC connect line is also in the statement above the start multiple statements line.

Regards,

Daniel

Re: SQL Load

Anonymous — Mon, 21 Oct 2013 15:44:08 GMT

The error message may be misleading. I see at least one place to fix. In this load:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS TIMEMODIFIED
WHERE floor(TIMEMODIFIED' = num(today());SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

There is field TIMEMODIFIED twice - in * and by itself. Try to change a little:

LOAD *,
Date(TIMEMODIFIED,'DD/MM/YYYY') AS DateModified // different name
WHERE floor(TIMEMODIFIED' = num(today());
SQL SELECT *
FROM "IXOMEX_OWNER".ZZQUOTEPRICE;

Re: SQL Load

danielnevitt — Mon, 21 Oct 2013 15:53:53 GMT

Hi Michael,

Thanks again for the reply. Unfortunately the script now returns 0 results.

The TIMEMODIFIED column is TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]', so I'm not sure whether this is what's causing the issue?

Regards,

Daniel

Re: SQL Load

Anonymous — Mon, 21 Oct 2013 16:04:28 GMT

I take care about time format with floor() function. The floor(TIMEMODIFIED) returns integer, that is the time part (hh:mm:ss [.fff]) is removed and the date part stays.
Can you verify in the database directly if there are records with today's date?

And, I see my mistyping, it should be:

WHERE floor(TIMEMODIFIED) = num(today());