Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like this:
TableA:
Load
*
From TABLEASource;
TableB:
Load
*
From TABLEBSource
Where exists(UserID, UserID);
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
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;
Hi Johannes,
Isn't LEFT JOIN will extract all data from the LEFT(TABLE A) no matter what value on the RIGHT (TABLE B)?
Hi Johannes,
Isn't LEFT JOIN will extract all data from the LEFT(TABLE A) no matter what value on the RIGHT (TABLE B)?
Yes you are right.
use INNER JOIN to keep only the records in TableA that has a matching record in TableB