where clause in QVD load

[#1]

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

[danielrozental]

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?

[#3]

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.

[#4]

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. 

[sherwinzxw]

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

[passionate]

Try something like below:

LOAD * INLINE [

    State

    CA

];

LOAD [First Name],

     [Last Name],

     State,

     Title,

     Company,

     ActivityDate

FROM

(qvd) WHERE Exists (State);