Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to filter out NULL value records

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

Tags (3)
6 Replies
jsn
Honored Contributor

How to filter out NULL value records

Something like this:

TableA:
Load
*
From TABLEASource;

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

Not applicable

How to filter out NULL value records

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

jsn
Honored Contributor

How to filter out NULL value records

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;

Not applicable

How to filter out NULL value records

Hi Johannes,

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

Not applicable

How to filter out NULL value records

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
Valued Contributor

SV:Re: How to filter out NULL value records

Yes you are right.

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

Community Browser