topic How to filter out NULL value records in QlikView

How to filter out NULL value records

Thu, 03 Mar 2011 05:27:56 GMT

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

How to filter out NULL value records

Anonymous — Thu, 03 Mar 2011 05:49:23 GMT

Something like this:

TableA:
Load
*
From TABLEASource;

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

How to filter out NULL value records

Thu, 03 Mar 2011 07:18:40 GMT

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

How to filter out NULL value records

Anonymous — Thu, 03 Mar 2011 07:24:54 GMT

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;

How to filter out NULL value records

Thu, 03 Mar 2011 08:19:23 GMT

Hi Johannes,

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

How to filter out NULL value records

Thu, 03 Mar 2011 08:19:33 GMT

Hi Johannes,

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

SV:Re: How to filter out NULL value records

gandalfgray — Thu, 03 Mar 2011 16:27:37 GMT

Yes you are right.

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