topic Re: SQL select data with date filter in QlikView

SQL select data with date filter

Tue, 05 Aug 2014 15:18:26 GMT

Hi, everybody.

I have a QV project with data import from MS SQL via script.

I use the SQL statement for data import and I'd like to filter the date-field DATUMOBDOBI:

SQL SELECT ...., DATUMOBDOBI, ....

FROM "rsv2_stav".dbo."qryDatovySklad_QlikView"

Where DATUMOBDOBI > vUzavreneObdobi;

vUzavreneObdobi is date-variable.

Values / Formats of DATUMOBDOBI and vUzavreneObdobi look like this:

I have tried all possible syntax combinations (one of them see below), but I receive an error.

Please help.

Thanx in advance.

Duski

Re: SQL select data with date filter

PrashantSangle — Tue, 05 Aug 2014 15:22:56 GMT

Hi,

You are trying to use Qlikview Syntax in SQL Query.

Instead of try normal SQL Query;

Then Use Resident load and use Where clause which you are trying.

Regards

Re: SQL select data with date filter

PrashantSangle — Tue, 05 Aug 2014 15:27:49 GMT

Hi,

Try like this

test:

SQL SELECT ...., DATUMOBDOBI, ....

FROM "rsv2_stav".dbo."qryDatovySklad_QlikView"

Where DATUMOBDOBI > vUzavreneObdobi;

Final_test:

load *

resident test

where date(date#(DATUMOBDOBI,'DD.MM.YYYY')>Date(41791)

Drop table test;

Regards,

Re: SQL select data with date filter

israrkhan — Tue, 05 Aug 2014 15:46:31 GMT

Because one side you have Number and other side datetime, thats y your getting error.

you need to convert Number to date, like Where DATUMOBDOBI > Date(vUzavreneObdobi);

and can you show me where you are declaring and initializing vUzavreneObdobi.

OR Initialize your variable like Date(YourDateField), than in the where clause you dont need to change anything.

Secondly you can convert your Date to number like,

try like Num(DATUMOBDOBI) > vUzavreneObdobi;

hope it helps

Re: SQL select data with date filter

Wed, 06 Aug 2014 06:30:55 GMT

Thank you, max dreamer for your responce, but nothing worked 😞

I helped myself with this SQL statement, which after all worked:

SQL SELECT ..., DATUMOBDOBI as Obdobie, ...

FROM "rsv2_stav".dbo."qryDatovySklad_QlikView"

WHERE DATUMOBDOBI > (SELECT max(UZAVRENEOBDOBI)

FROM "rsv2_stav".dbo."qryUzavreteObdobie");

Thank you very much for your time 🙂

Duski

Re: SQL select data with date filter

Wed, 06 Aug 2014 06:31:20 GMT

Thank you, IsrarKhan for your responce, but nothing worked 😞

I helped myself with this SQL statement, which after all worked:

SQL SELECT ..., DATUMOBDOBI as Obdobie, ...

FROM "rsv2_stav".dbo."qryDatovySklad_QlikView"

WHERE DATUMOBDOBI > (SELECT max(UZAVRENEOBDOBI)

FROM "rsv2_stav".dbo."qryUzavreteObdobie");

Thank you very much for your time 🙂

Duski