Anti join - does set analysis force an inner join?

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

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!

[#4]

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.

[Miguel_Angel_Baeyens]

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