topic Re: Anti join - does set analysis force an inner join? in QlikView

Anti join - does set analysis force an inner join?

Wed, 12 Sep 2012 10:50:48 GMT

Hi all,

Attached is a test QVW.

The scenario is this - you want to count those people that appear on Table 1, but NOT on Table 2. I have tried using set analysis (you will see in Options 1 and 2) but it appears that an inner join is forced. So I have to use an IF statement (option 3) that works fine.

Can someone confirm that in QV a set analysis always forces an inner join when variables from two (or more) tables are included in the set statement?

Further - if anyone knows a way to do this anti join using Set analysis then I'd love to hear it.

Thanks

Gareth

Re: Anti join - does set analysis force an inner join?

Miguel_Angel_Baeyens — Wed, 12 Sep 2012 11:05:59 GMT

Hi,

Technically, what QlikView does is link two tables as long as they share at least one field with the same name, case sensitive. What the listbox shows is the list of possible different values in that field.

So if you want to keep this link, but show which values are where, I'd duplicate the fields in the script with different names, then use a flag field using the function Exists() for example, to create a 1 or 0 value flag field that you will use later to count the differences, something like the following:

=Count({1 - < FLG = {1} >} DISTINCT ID)

That reads: return the number of distinct values in field ID that do not have FLG = 1.

Hope that gives you an idea.

Miguel

Re: Anti join - does set analysis force an inner join?

Wed, 12 Sep 2012 11:09:13 GMT

Great! Thanks Miguel, I have not used the 1 - before.

One thing QlikTech could do better is the documentation around set analysis. Just a suggestion.

Much appreciated!

Re: Anti join - does set analysis force an inner join?

Wed, 12 Sep 2012 11:14:37 GMT

Just as a further note. The scenario I want is ID's on one table but not on another table. So the full code I used looks like this:

count({1 - <FLG={1}>*<TFLG={1}>} distinct ID)

Where TFLG={1} is on the table I WANT to count and FLG={1} is the flag on the table I want to EXCLUDE.

Worked a treat.

Re: Anti join - does set analysis force an inner join?

Miguel_Angel_Baeyens — Wed, 12 Sep 2012 11:32:33 GMT

Hi,

I totally agree on the need of a better documentation and I'm aware that are people within QlikView working on it.

Thanks for your remarks.

Miguel