Question: Eliminating Specific Codes

[#1]

If I have 2 tables already loaded in my system:

Table 1 has fields "BadCode" and "Description" and here are a few mocked recs:

002, Bad Code

003, Another Bad Code

Table 2 has fields "Rec#", "Time", "Date", "Account", "Code" and here are a few mocked recs:

01,040000,20100101,00001,001

02,050000,20100101,00001,002

03,040011,20100202,00002,003

04,040022,20100901,00003,001

How would I produce a Third Table where I have the same field layout as Table 2 , but not display any of the records in which a bad code is contained (matching Table1.BadCode and Table2.Code) I'd want Table3 to contain Rec# 01 and Rec# 04 as they contain a Code not found in Table1

I would assume in SQL it would be something like:

SELECT * FROM TABLE2 WHERE CODE NOT IN (SELECT BADCODE FROM TABLE1)

But not sure how this would work in QlikView syntax.

Thanks for any guidance,

Miles

[johnw · Accepted solution]

BadCodes:
LOAD BadCode
FROM ...
;
GoodRecs:
LOAD
Rec#
,Time
,Date
,Account
,Code
FROM ...
WHERE not exists(BadCode,Code)
;
DROP TABLE BadCodes;

[#3]

Thanks John! Worked great.

[#4]

Just as a follow-up question, I could. If I wanted to change to including a field in the GoodRecs: table which would be an indication of whether it would be considered a good record or not, how would I go about it... could I use an IF:

if(Code in (select BadCode from BadCodes),'Yes','No') AS IsTheCodeBad

or

if(Code in BadCode),'Yes','No') AS IsTheCodeBad

or

if(Code = BadCode),'Yes','No') AS IsTheCodeBad

?

Thanks for any help,

[johnw]

I think this:

if(exists(BadCode,Code),'Yes','No') as IsTheCodeBad

And of course drop the "where not exists" in the load.

[#6]

Thx again John. What are you going to do with all those points? Can you trade them in for Air Miles?

[johnw]

Heh. My boss told me he wished we could trade them for software and license discounts.