where clause in load from qvd

[#1]

Hi all,

I can't find the right place in the documentation.

I want to filter null and non-numeric values in the load statement below. I am selecting from a QVD. What flavor of SQL does QV use when selecting from a QVD? Is there an isnumeric() like function?

Thanks

LOAD

QBrief as QID,

QID3 as QGroup,

QFull

FROM

c:\temp\Question_Master.QVD (qvd)

where

QID3 is not null (something like this)

and isnumeric(QID3) (something like this)

[#2]

Hi,

use

not(isnull(YOURFIELD))!!

isNum()

Be careful!

Evaluation true gives you a "-1"!

Cheers

Martin

[#3]

LOAD

xxxx,

yyyy

FROM $(QVDPATH)\qvdNAME.QVD (qvd)

Where ISNULL(your field) =0 ; (DONT FORGET ' ; ' )

[#4]

thanks for the quick reply !

It is a string field which can have values like (A,B,C) as well as (1,2,3).

In this case, all I want is the strings that evaluate to numbers. I do not know ahead of time what those numbers might be.

Thoughts?

[#5]

I dont now if i understood, but you can try something like case or IF to change to number or AUTONUMBERHASH128(your field)...

take a look about this function in help of QlikView!!!!!

[johnw · Accepted solution]

I suspect this would work:

WHERE len(num(QID3))

But if it were me, I'd use that expression to set a flag or type or something when BUILDING the QVD to identify this type of QID3 value. Let's say you established a "QID3 is Numeric?" flag. Then you would do your load like this:

[Data]:
LOAD 'Y' as "QID3 is Numeric?"
AUTOGENERATE 1;

INNER JOIN ([Data])
LOAD
QBrief as QID,
QID3 as QGroup,
QFull,

"QID3 is Numeric?" // Edit: Added this because I missed it on the first pass

FROM c:\temp\Question_Master.QVD (qvd)
WHERE exists("QID3 is Numeric?");

DROP FIELD "QID3 is Numeric?";

The advantage is that this will produce an optimized QVD load, which is much faster than an unoptimized QVD load.

[#7]

I'll check that function out. You are right, my message is not very clear.

the field values can contain values like the ones below

A1, A2, A3, A4, 1, 2, 3, 56, 77, 89, 100 (or any valid numbers)

In the where clause, I want to eliminate any records with values like A1, A2, A3, A4 and return the ones with valid numbers like 1, 2, 3, 56, 77, 89, 100.

I was hoping for an isnumber() function to solve the problem.

[#8]

oooo, nice.

thanks

[#9]

Thanks to all who responded. All the suggestions put me on the right track. The final where clause looks like this....

where

isnull(QID3) = 0

and IsNum(QID3) = -1

[#10]

Please close this thread by verifying an answer