How to filter out NULL value records

[#1]

HI all,

Perhaps this question sounds like simple, but I am not able to figure this out.

I have 2 tables:

Table A - have report details with the owner user ID

Table B - Active User information

So, table A owner ID should be link up with Table B. But some records may not able to find a value in B since the user left the firm already. Thus, I would like to take out all the records that do not have a matched receord in B using JOIN.

How can I do so?

I tried to use INNER JOIN / LEFT JOIN but this does not work.

I also tried to create a TEMP table first with the JOIN and LOAD another table using the RESIDENT TEMP table where NOT isnull(UserName), also does not work.

Thanks all

[Anonymous]

Something like this:

TableA:
Load
*
From TABLEASource;

TableB:
Load
*
From TABLEBSource
Where exists(UserID, UserID);

[#3]

Thank you Johannes.

But I don't understand your code mean (Sorry that I am new to QlikView).

What I want is to embed user information in TABLE B into the report information table (TABLE A) with JOIN statement. Final outcome something like

TABLE A : Reportname, ReportDesc, ReportDate, OwnerID

TABLE B : UserID, UserName, UserTeam

OUTCOME TABLE C : Reportname, ReportDesc, ReportDate, UserName, UserTeam

Thanks

[Anonymous]

Ah, I completely misunderstood the question. You're looking to do a join (either left join or right join, depending on what values you want to keep). In this case I assume that you only want to load values from Table B where the UserID exists as OwnerID in TableA, correct?

TableC:
Load
Reportname,
ReportDesc,
ReportDate,
OwnerID as UserID
From TABLEASOURCEDATA;

LEFT JOIN //This only keeps records where UserID in table B matches OwnerID in table A.

Load
UserID,
UserName,
UserTeam
From TABLEBSOURCEDATA;

[#5]

Hi Johannes,

Isn't LEFT JOIN will extract all data from the LEFT(TABLE A) no matter what value on the RIGHT (TABLE B)?

[#6]

Hi Johannes,

Isn't LEFT JOIN will extract all data from the LEFT(TABLE A) no matter what value on the RIGHT (TABLE B)?

[gandalfgray]

Yes you are right.

use INNER JOIN to keep only the records in TableA that has a matching record in TableB