topic Re: Filtered Left Join with Set Analysis in QlikView

Filtered Left Join with Set Analysis

lbunnell — Fri, 10 Jul 2015 12:59:23 GMT

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.

Re: Filtered Left Join with Set Analysis

sunny_talwar — Fri, 10 Jul 2015 13:08:25 GMT

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

Re: Filtered Left Join with Set Analysis

hic — Fri, 10 Jul 2015 13:12:02 GMT

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

Re: Filtered Left Join with Set Analysis

sunny_talwar — Fri, 10 Jul 2015 13:12:23 GMT

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

Re: Filtered Left Join with Set Analysis

lbunnell — Fri, 10 Jul 2015 13:33:49 GMT

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

Re: Filtered Left Join with Set Analysis

sunny_talwar — Fri, 10 Jul 2015 13:41:55 GMT

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'}>}

Re: Filtered Left Join with Set Analysis

lbunnell — Fri, 10 Jul 2015 14:42:25 GMT

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

Re: Filtered Left Join with Set Analysis

sunny_talwar — Fri, 10 Jul 2015 14:46:24 GMT

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

Best,

Sunny