Filtered Left Join with Set Analysis

[lbunnell]

I have two tables from separate QVDs auto joined in my presentation layer on a common 'Title' field.

Table 1:

Title

Format

Status

...other fields...

Table 2:

Title

Username

Sessions

...other fields...

Table 1 contains all of the records and Table 2 contains a subset of the records. I'm trying to get a set analysis expression for a chart straight table that will show all of the rows from Table 1 that do not have a corresponding record in Table 2. Since none of the records in Table 1 have a Username field, I thought something like this might work

=count({1-$<Username={"*"}>} Distinct Title)

However, I'm still only getting the records that are in both tables. I'd also like to only get the records with a Format="Cognos" and Status="Active"

Any help is appreciated.

[sunny_talwar]

Try this with a calculated dimension, may be like this:

If(IsNull(Username), Title) and check the option 'Suppress When Value Is Null' on dimension tabs under properties.

Attaching a sample to look at:

Best,

Sunny

[hic]

Try

Count( {$<Title=E({1<Username={"*"}>} Title )>} distinct Title )

In other words: The E() function will return the set of Titles that are excluded if you select all Usernames.

HIC

[sunny_talwar]

Or if you want to use set analysis, you can try to use this:

[lbunnell]

Thanks, that seems to do it! Now how to solve for Format=Cognos and Status=Active?

[sunny_talwar · Accepted solution]

Not sure which approach you are using. With Calculated Dimension you can do this:

If(IsNull(Username) and Format = 'Cognos' and  Status = 'Active', Title)

With Set Analysis you can try this:

{<Title=e({<Username={'*?'}>})> * <Format = {'Cognos'}> * <Status = {'Active'}>}

[lbunnell]

The set analysis version works. Not sure why expression wouldn't work, but perhaps the field may have some value other than null.

[sunny_talwar]

The first one should have worked also, but I am glad that the set analysis option worked out for you.

Best,

Sunny