Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
tom2qlik
Creator
Creator

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
Master
Master

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
Master
Master

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
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

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

Thanks for your input.