topic Question: Eliminating Specific Codes in QlikView

Question: Eliminating Specific Codes

Thu, 02 Sep 2010 21:48:20 GMT

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

Question: Eliminating Specific Codes

johnw — Thu, 02 Sep 2010 21:52:29 GMT

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

Question: Eliminating Specific Codes

Fri, 03 Sep 2010 14:18:18 GMT

Thanks John! Worked great.

Question: Eliminating Specific Codes

Thu, 09 Sep 2010 17:07:24 GMT

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

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

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

Thanks for any help,

Question: Eliminating Specific Codes

johnw — Thu, 09 Sep 2010 18:24:19 GMT

I think this:

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

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

Question: Eliminating Specific Codes

Tue, 14 Sep 2010 18:51:51 GMT

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

Question: Eliminating Specific Codes

johnw — Wed, 15 Sep 2010 00:13:50 GMT

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