topic Excluding data in set analysis in QlikView

Excluding data in set analysis

Tue, 31 May 2011 12:16:13 GMT

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?

Excluding data in set analysis

Wed, 01 Jun 2011 03:46:50 GMT

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)

Excluding data in set analysis

Wed, 01 Jun 2011 10:08:47 GMT

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

Excluding data in set analysis

Wed, 01 Jun 2011 10:26:10 GMT

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.

Re: Excluding data in set analysis

Miguel_Angel_Baeyens — Wed, 01 Jun 2011 10:26:58 GMT

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

Excluding data in set analysis

Wed, 01 Jun 2011 10:42:28 GMT

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!

Excluding data in set analysis

Wed, 01 Jun 2011 11:02:36 GMT

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)

Excluding data in set analysis

Wed, 01 Jun 2011 11:31:25 GMT

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