Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
tom2qlik
Contributor

Where not Exists not working

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

1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Where not Exists not working

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

View solution in original post

4 Replies
effinty2112
Honored Contributor

Re: Where not Exists not working

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

View solution in original post

Re: Where not Exists not working

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

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
Contributor

Re: Where not Exists not working

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

Thanks for your input.