topic where clause in QVD load in QlikView

where clause in QVD load

Mon, 18 Jul 2011 14:26:57 GMT

Hi,

Any idea why this script is ignoring the Where clause? It should only load records where STATE = 'CA', but it just loads everything.

LOAD [First Name],

[Last Name],

State,

Title,

Company,

ActivityDate

FROM

(qvd)

WHERE(State = 'CA');

Thanks

where clause in QVD load

colinh — Mon, 18 Jul 2011 14:33:43 GMT

Try it without the brackets: WHERE State = 'CA';

where clause in QVD load

Mon, 18 Jul 2011 14:34:58 GMT

Check for the Space in Data

you can use trim function

LOAD [First Name],

[Last Name],

State,

Title,

Company,

ActivityDate

FROM

(qvd)

Where trim(State) = 'CA';

where clause in QVD load

Mon, 18 Jul 2011 14:47:41 GMT

I tried Trim and I tried removing the brackets. No better. The original code was actually generated with the load wizard, incidentally.

where clause in QVD load

colinh — Mon, 18 Jul 2011 14:49:47 GMT

In your data is the value 'ca' or 'CA'? Try where upper(State) = 'CA';

where clause in QVD load

Mon, 18 Jul 2011 14:53:04 GMT

There are lots of different values but definitely some having CA. But my problem is that all data gets loaded, not that some are being wrongly excluded.

FYI, I originally was trying to select on a numeric value but the Where clause didn't work for that one either.

where clause in QVD load

colinh — Mon, 18 Jul 2011 15:15:53 GMT

Very odd. Using the wizard to create an almost identical statement works fine for me.

What version / service release of QlikView are you using?

where clause in QVD load

Mon, 18 Jul 2011 15:26:13 GMT

Yes, agree it's weird. Either something in my data or there's a higher level setting someplace. But I haven't changed any defaults.

If it matters, I'm running Windows 7 64 bit, and running QV 10.00.8815.6 SR1 64-bit. Maybe I'll try downloading some patches.

where clause in QVD load

colinh — Mon, 18 Jul 2011 15:31:07 GMT

SR1 was, in my very short experience with it, very buggy. I would upgrade to SR3 and see if that fixes the problem.

where clause in QVD load

Mon, 18 Jul 2011 16:33:34 GMT

No, SR3 didn't fix it. Don't understand. Seems so simple.

Re: where clause in QVD load

danielrozental — Mon, 18 Jul 2011 16:38:56 GMT

Can you share your QVD/QVW file?

Is it doing an optimized load even if it has a WHERE statement? Are you sure you don't have an extra ";" before the WHERE?

Can you share your entire script? Are you sure you're not loading the file twice?

where clause in QVD load

Mon, 18 Jul 2011 16:42:33 GMT

I tried olafjoppe's approach of first loading the whole table and then using the Where clause in a resident load. That worked, for whatever reason.

Thanks everybody.

Re: where clause in QVD load

Mon, 18 Jul 2011 16:52:46 GMT

Must've been a SR1 problem. I created a new QVW after loading SR3, and copied-and-pasted the script from my previous version -- and it worked.

Re: where clause in QVD load

sherwinzxw — Tue, 02 Oct 2018 06:35:38 GMT

Hi David,

It's been quite a long while after you posted this question, but fact is even it's 2018 now, I still encounter the same issue.

However, I found a walk around for the issue (my version is QlikView 12), explained below:

[Field] from [QVD] being filtered using [WHERE] clause being won't work unless the filed is renamed, with a name different from the one you STORE into the QVD.

For example, I have below script, and in this case, after I renamed the RegionShortName to RegionShortCode, the WHERE Clause will work. (if not renamed, the two commented out WHERE clauses both won't work)

e.g.

--------------------------------------------------------

[Region]:

SQL SELECT

FROM dbo.Region;

STORE [Hospital] into [qvd\Hospital].qvd;

[Region]:

LOAD

Region,

RegionShortName,

RegionShortName AS 'RegionShortCode'

FROM

[qvd\Region.qvd] (qvd)

WHERE RegionShortName = 'S';

// WHERE WILDMATCH(RegionShortName,'*S*');

// WHERE MixMatch(RegionShortName, 'S');

--------------------------------------------------------------

Hope the same work around will work for all.

Cheers,

Sherwin

Re: where clause in QVD load

passionate — Tue, 02 Oct 2018 15:06:04 GMT

Try something like below:

LOAD * INLINE [

State

];

LOAD [First Name],

[Last Name],

State,

Title,

Company,

ActivityDate

FROM

(qvd) WHERE Exists (State);