topic Loading from QVD in QlikView

Loading from QVD

Wed, 16 Nov 2011 15:25:48 GMT

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!

Loading from QVD

Wed, 16 Nov 2011 15:29:03 GMT

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'

Loading from QVD

Michiel_QV_Fan — Wed, 16 Nov 2011 15:53:26 GMT

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')

Loading from QVD

Wed, 16 Nov 2011 16:07:03 GMT

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!

Loading from QVD

Wed, 16 Nov 2011 16:11:48 GMT

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

Loading from QVD

Wed, 16 Nov 2011 16:19:19 GMT

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'

Re: Loading from QVD

johnw — Wed, 16 Nov 2011 19:56:44 GMT

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.