topic Set Analysis - Intersection in QlikView

Set Analysis - Intersection

Fri, 18 Mar 2011 20:44:47 GMT

PROBLEM STATEMENT: Ability to view Name without "Active" that Share a "ShareID" with Name that do have "Active"

I am able to identify the Name that meets the intersection criteria but when it comes to displaying in a chart I am unable to get the desired output format chart.

Can someone please help me.

I have included QVW file and below is my data setup.

Thank you in advance!!

Pranita

Data Setup:

Name Table:

Name, KeyID,NameID

Randy, 1, 1

David,2,2

Andy,3,3

Mandy,4,4

ShareID

KEYID, ShareID

1, 10313237

2, 10313237

3 , 10313237

4, 10313237

TYPE Table:

TYPEID, TYPEDESC

1299 ,Active

10884

10887

10935

Desired Chart Output

SharedID, Name_With_Active, Name_without_Active

10313237, Mandy ,Andy

10313237, Mandy ,David

10313237 , Mandy, Randy

Set Analysis - Intersection

Oleg_Troyansky — Fri, 18 Mar 2011 21:46:54 GMT

Pranita,

the formula to calculate what you need is the following:

=count({<TYPEDESC ={"<>'Active'"}, ShareID = {"=count({<TYPEDESC = {'Active'}>} NAMEID)>0"} >} Name)

however, it won't work as long as "Active" is the only possible value in the field "TYPEDESC". You can't select TYPEDESC <> 'Active" if there are no more choices. If you replace nulls with "Not Active" or even spaces, it should work then.

To explain this formula:

- you are counting those Names that have TYPEDESC other than "active", however, they are associated with ShareID that satisfy the following condition: the count of associated NAMEID that are active, is greater then 0.

hope it all makes sense.

cheers,

Set Analysis - Intersection

johnw — Fri, 18 Mar 2011 21:52:47 GMT

I don't really understand your data, so I don't know if this is the right answer. However, I can generate your desired chart like this:

dimension 1 = ShareID
dimension 2 = Name
expression = if(TYPEDESC<>'Active',only({<TYPEDESC={'Active'}>} total Name))

Set Analysis - Intersection

Anonymous — Sun, 20 Mar 2011 17:39:54 GMT

Maybe this?
Notice that there is no set analysis there. Dimensions:
- KeyID (hidden)
- ShareID
- aggr(only(total if(TYPEDESC='Active', Name)), Name)
Expression
only(if(TYPEDESC<>'Active' , Name))