Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excluding data in set analysis

I need to count the Customers (CON_CUSTOMER) where there are no matches for SME_Person 
and LinkContact.  I have written the following set analysis but Qlikview is picking up both matches and non-matches

count({$<CON_CUSTOMER = E({1<SME_Person = {LinkContact}>})>}CON_CUSTOMER)

Can anyone shed any light on where I'm going wrong?

1 Solution

Accepted Solutions
Not applicable
Author

Ohh sorry!!

I was about to write this only:

count( {$<SME_Person= E({1<LinkContact={'*'}>}) >} CON_CUSTOMER)

May be in hurry I wrote

count( {$<CON_CUSTOMER= E({1<LinkContact={'*'}>}) >} CON_CUSTOMER)

View solution in original post

7 Replies
Not applicable
Author

I think U r missing the Quotes..

Try Thsi:

count({$<CON_CUSTOMER = E({1<SME_Person = {'LinkContact'}>})>}CON_CUSTOMER)

or else try this:

count({<SME_Person ={'*'} - {'LinkContact'}>}CON_CUSTOMER)

Not applicable
Author

Thanks Erika but LinkContact is a field.  I tried amending your second example by taking out the quotes but that doesn't work either.

LinkContact is a field in a different QVD to SME_Person joined by a key field. I want to find all the SME_Person that do not have a matching LinkContact

Not applicable
Author

oh sorry !!

I thought

Link Contact is a Value and not field.

Try this:

count( {$<CON_CUSTOMER= E({1<LinkContact={‘*'}>})>} CON_CUSTOMER)

There  may be some syntx errors.Plz correct them.

Miguel_Angel_Baeyens

Hello Andy,

Assuming the following data

Persons:

LOAD * INLINE [

Code, SME_Person

1, John

2, Michael

3, Peter

];

LinkContacts:

LOAD * INLINE [

Code, LinkContact

1, JohnsLink

3, PetersLink

];

And creating a pivot table with "Code" as Dimension and the following as expression should return as expected (1 count for code 2 who doesn't have a link contact)

Count({1 - < LinkContact = P(LinkContact) >} SME_Person)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Erika - I just changed CON_CUSTOMER to SME_Person and it worked perfectly

count( {$<SME_Person= E({1<LinkContact={'*'}>}) >} CON_CUSTOMER)

Thank you for your help!

Not applicable
Author

Ohh sorry!!

I was about to write this only:

count( {$<SME_Person= E({1<LinkContact={'*'}>}) >} CON_CUSTOMER)

May be in hurry I wrote

count( {$<CON_CUSTOMER= E({1<LinkContact={'*'}>}) >} CON_CUSTOMER)

Not applicable
Author

Hello Miguel

I had a look at what you have suggested and I can't seem to get what you suggest work in my example although your logic looks good. As Erika has solved my problem I'll go with that!

Thanks for your time

Andy