Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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?

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Excluding data in set analysis

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)

7 Replies
Not applicable

Excluding data in set analysis

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

Excluding data in set analysis

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

Excluding data in set analysis

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.

MVP
MVP

Re: Excluding data in set analysis

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

Excluding data in set analysis

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

Excluding data in set analysis

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

Excluding data in set analysis

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

Community Browser