topic Re: Where not Exists not working in QlikView

Where not Exists not working

tom2qlik — Thu, 31 Aug 2017 13:36:22 GMT

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

Re: Where not Exists not working

effinty2112 — Thu, 31 Aug 2017 13:54:39 GMT

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

Re: Where not Exists not working

Peter_Cammaert — Thu, 31 Aug 2017 13:57:51 GMT

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

Re: Where not Exists not working

its_anandrjs — Thu, 31 Aug 2017 14:02:43 GMT

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*');

Re: Where not Exists not working

tom2qlik — Thu, 31 Aug 2017 14:32:26 GMT

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

Thanks for your input.