Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rbecher
MVP
MVP

Just do this:

1. load EMPLID from <Excel sheet>

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

- Ralf

Astrato.io Head of R&D

View solution in original post

7 Replies
rbecher
MVP
MVP

Just do this:

1. load EMPLID from <Excel sheet>

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

- Ralf

Astrato.io Head of R&D
rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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.

rbecher
MVP
MVP

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

Ralf

Astrato.io Head of R&D
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Author

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