Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to exclude rows from the results of a select

I'm wanting to load data from a select statement where the emplid is not in a loaded table from a spread sheet. In other words, I load a spread sheet of EMPLID's and then I want to load the records from the HR database only for those EMPLID's that were on the spread sheet. I can't figure out if I can do a load of the select statement with a where clause and then use a sub select for where the emplid exists in the QVD file, or something like that. Any Ideas?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

How to exclude rows from the results of a select

Just do this:

1. load EMPLID from <Excel sheet>

2. load
EMPLID, ...
from <HR database>
WHERE exists(EMPLID);

- Ralf

View solution in original post

7 Replies
MVP & Luminary
MVP & Luminary

How to exclude rows from the results of a select

Just do this:

1. load EMPLID from <Excel sheet>

2. load
EMPLID, ...
from <HR database>
WHERE exists(EMPLID);

- Ralf

View solution in original post

MVP & Luminary
MVP & Luminary

How to exclude rows from the results of a select

Maybe I got it wrong in the first place. But you could try this:

2. load
EMPLID, ...
WHERE exists(EMPLID);
select * from <HR database>;

I never tried this, would be interesting if this works...

- Ralf

Not applicable

How to exclude rows from the results of a select

The syntax was accepted, however, as far as I can tell, it did not reduce the data. It still pulled in 6000 rows rather than 400 rows.

MVP & Luminary
MVP & Luminary

How to exclude rows from the results of a select

It should work if the field name EMPLID is the same in both loads. Can you upload your script code?

Ralf

mov
Esteemed Contributor III

How to exclude rows from the results of a select

I've been using this method all the time. It works and it is reliable. Something is missing in the information provided.

Not applicable

How to exclude rows from the results of a select

I found my problem. I had another select on another tab that also was using the EMPLID, pulling in all of the records. By using the

WHERE exists(EMPLID) on this load, and on the load I was working with, it resolved the problem.

Thanks for you help!

Not applicable

How to exclude rows from the results of a select

Hello,

I've been using the text

Where not [Projection Period] like '0';

This has allowed me to exclude all the Projection periods which have a 0 for the value. Now I need to exclude another value from another field. I initially tried

Where not [Projection Period] OR [Scenario] like '0' OR 'X';

Where X represents the scenario I wish to exclude and while it works it also slowed the load statement to a crawl. I'm sure there is a way to split these up and I just don't know how. Could you offer any advice?

Many Thanks