Where not Exists not working

[tom2qlik]

Hello,

I am attempting to load data to a table (Missing Products) which does not exist in an existing table (Table1).

Problem is the Missing products table loads in all values regardless if they exist in Table1 or not.

I was under the impression a where not exists(Field) would do the trick however since the result is not as expected maybe I've misunderstood the function.

Table1:
Load
BriefRefNo as P_ID,
ProductDescription as P_Desc,
BriefRefNo&' | '&ProductDescription as ProductKey
FROM
[Products.qvd]
(qvd);

MissingProducts:
Load ProductKeyNo,
ProductKey, 
P_Comment
where Not Exists(ProductKey);
Load
ProductKeyNo,
ProductKeyNo&' | '&P_Comment as ProductKey,
trim(P_Comment) as P_Comment
where not IsNull(P_Comment);
Load
BriefRefNo as ProductKeyNo,
if(right(Comment, 😎 = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment
FROM
[ChangeHistory.qvd]
(qvd)
where WildMatch(Comment, '*active*');

The Missing Products table returns everything in that QVD instead of what's not in Table1

Anyone have idea where I'm going wrong here?

Thanks,

Tom

[effinty2112 · Accepted solution]

Hi Tom,

Try this:

Table1:

Load

BriefRefNo as P_ID,

ProductDescription as P_Desc,

BriefRefNo&' | '&ProductDescription as ProductKey

FROM

[Products.qvd]

(qvd);

MissingProducts:

Load ProductKeyNo,

ProductKey, 

P_Comment;

//where Not Exists(ProductKey);

Load

ProductKeyNo,

ProductKeyNo&' | '&P_Comment as ProductKey,

trim(P_Comment) as P_Comment

where not IsNull(P_Comment)

and Not Exists(ProductKey, ProductKeyNo&' | '&P_Comment);

Load

BriefRefNo as ProductKeyNo,

if(right(Comment, 😎 = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment

FROM

[ChangeHistory.qvd]

(qvd)

where WildMatch(Comment, '*active*');

Cheers

Andrew

[Peter_Cammaert]

Are you sure both ProductKey values are formatted identically? Did you check leading/trailing spaces or different comment values?

[its_anandrjs]

You can try this also comment extra preceding load why this required you don't required that.

  Table1:
Load
BriefRefNo as P_ID,
ProductDescription as P_Desc,
BriefRefNo&' | '&ProductDescription as ProductKey
FROM
[Products.qvd]
(qvd);

MissingProducts:
//Load ProductKeyNo,
//ProductKey, 
//P_Comment;
//where Not Exists(ProductKey);
Load
ProductKeyNo,
ProductKeyNo&' | '&P_Comment as ProductKey,
trim(P_Comment) as P_Comment
where not IsNull(P_Comment) and Not Exists(ProductKey,ProductKeyNo&' | '&P_Comment);

Load
BriefRefNo as ProductKeyNo,
if(right(Comment, 😎 = 'Inactive', TextBetween(Comment, 'Product', 'Changed to Inactive'), TextBetween(Comment, 'Product', 'Changed to Active'))  as P_Comment
FROM
[ChangeHistory.qvd]
(qvd)
where WildMatch(Comment, '*active*');

[tom2qlik]

You are correct as I noticed this after posting the question.  Still produced an unexpected result.

Thanks for your input.