Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
tom2qlik
New Contributor III

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, 8) = '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, 8) = '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

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, 8) = '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

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, 8) = '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
New Contributor III

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.

Community Browser