Loading from QVD

[#1]

Hi,

I am loading some fields from QVD and I want to narrow my information by adding "where"

So my query looks like:

Select * from ..\QVD\Putbom.qvd(qvd)

where

C.#COENT = 40

AND DATE(SUBSTR(DIGITS(#COEDT), 5, 2)||'/' || SUBSTR(DIGITS(#COEDT), 7, 2)||'/'||SUBSTR(DIGITS(#COEDT), 1, 4)) > $(varEndOfYearDate);

I am getting the below error:

ODBC connection failed

Select * from ..\QVD\Putbom.qvd(qvd)

where

C.#COENT = 40

AND DATE(SUBSTR(DIGITS(#COEDT), 5, 2)||'/' || SUBSTR(DIGITS(#COEDT), 7, 2)||'/'||SUBSTR(DIGITS(#COEDT), 1, 4)) > '06/30/2010'

Can anybody help me please!

[#2]

I changed the Select to Load but I got the below error:

Error in expression:

SUBSTR is not a valid function

QVDLoad3:

Load * from ..\QVD\Putbom.qvd(qvd)

where

C.#COENT = 40

AND DATE(SUBSTR(DIGITS(#COEDT), 5, 2)||'/' || SUBSTR(DIGITS(#COEDT), 7, 2)||'/'||SUBSTR(DIGITS(#COEDT), 1, 4)) > '06/30/2010'

[Michiel_QV_Fan]

That's because substr is not a Qlikview function. And I doubt this will work anyway.

Not tested:

you could try:

DATE(COEDT) > DATE#(06/30/2010, 'DD/MM/YYYY')

[#4]

the query is working in Qlikview when I select directly from a table, i am getting the error when I load from QVD and and then add the where query!

[#5]

Hi,

while loading from qvd you can use the function mid() instead of substr().

If you LOAD data from qvd xls or csv etc. you need to use QV Syntax. If you load (with SQL SELECT...) from an external database with using SQL you can use the database language that you are selecting from.

Regards,

Björn

[#6]

I tried that and I got this error

Error in expression:

DIGITS is not a valid function

QVDLoad3:

Load * from ..\QVD\Putbom.qvd(qvd)

where

Company = 40

AND DATE( MID(DIGITS(#COEDT), 5, 2)||'/' || MID(DIGITS(#COEDT), 7, 2)||'/'||MID(DIGITS(#COEDT), 1, 4)) > '06/30/2010'

[johnw]

badrharfoush wrote:

I tried that and I got this error

Error in expression:

DIGITS is not a valid function

Right, because just like the error says, DIGITS() is not a valid QlikView function either.  You can only use valid QlikView functions in a LOAD statement. 

You may want to look up the documentation on the available QlikView functions and familiarize yourself with what is and isn't available.

When you're creating your QVD, you should make COEDT a QlikView date, not text, not a number.  Then:

LOAD *
FROM ..\QVD\Putbom.qvd (QVD)
WHERE Company = 40
  AND COEDT > makedate(2010,6,30);

I would never write it like that in a real application.  I never load *, and there are ways to drastically improve the performance of your filtering by COENT and COEDT.  But let's start with at least a runnable script and good data before we make things more complicated.