Why is Set Analysis on Key Fields Produces Unexpected Results?

[mbespartochnyy]

I ran into a VERY strange behavior in Qlik Sense set analysis. I'm hoping someone here can explain why set analysis is producing different/unexpected results on identical data set.

The app that demonstrates the issue is attached.

Context

I have one table that looks like this:

Here's the script for that table:

table_temp:
Load *
Inline [
id|name|name_qa_results|super_qa_results
1|name_1|Match|Match
2|name_2|Match|Match
3|name_3|Match|Match
4|name_4|Match|Match
5|name_5|Match|Match
6|name_6|Match|Match
7|name_7
8|name_8
9|name_9|Match|Match
10|name_10|Match|Match
11|name_11|Match|Match
12|name_12
13|name_13
14|name_14
15|name_15
16|name_16|Match|Match
](delimiter is '|');

table:
NoConcatenate
Load id
	, If(Len(Trim(name_qa_results)) = 0, Null(), name_qa_results) as name_qa_results
    , If(Len(Trim(super_qa_results)) = 0, Null(), super_qa_results) as super_qa_results
Resident table_temp;

Drop Table table_temp;

Then I have an expression in a text object that counts IDs that have a 'Match' value in super_qa_results field or an id number 7, like this:

Count({<super_qa_results = {'Match'}> + <id = {7}>} distinct id)

This expression produces accurate count which is 11:

The Problem

However, if I create an identical table that has two other tables associated with it and one of the associated tables has only one of all possible IDs, then the same expression produces the wrong count.

Here's what the data model for the second table looks like:

and here's the script to generate the second table along with the linked tables:

table_temp_2:
Load *
Inline [
id_2|name_2|name_qa_results_2|super_qa_results_2
1|name_1|Match|Match
2|name_2|Match|Match
3|name_3|Match|Match
4|name_4|Match|Match
5|name_5|Match|Match
6|name_6|Match|Match
7|name_7
8|name_8
9|name_9|Match|Match
10|name_10|Match|Match
11|name_11|Match|Match
12|name_12
13|name_13
14|name_14
15|name_15
16|name_16|Match|Match
](delimiter is '|');

table_2:
NoConcatenate
Load id_2
	, If(Len(Trim(name_qa_results_2)) = 0, Null(), name_qa_results_2) as name_qa_results_2
    , If(Len(Trim(super_qa_results_2)) = 0, Null(), super_qa_results_2) as super_qa_results_2
Resident table_temp_2;

link:
Load Distinct id_2
	, name_2
Resident table_temp_2
;

Drop Table table_temp_2;

dbOwner:
Load *
Inline [
id_2|db
16|db_1
16|db_2
16|db_3
16|db_4
](delimiter is '|');

and here's the screenshot of same text object with same expression (except it referencing fields from the second table, of course) generating wrong results:

Both table and table_2 have identical data, yet when I add two other tables to be associated with table_2, the set analysis starts to produce wrong results.

Any idea why set analysis is producing different results in this example?

[Qrishna]

I wasnt able to open your qvf.

I see there no change in the count for both the codes you have provided:

if you still encounter the same issue, i would suggest creating a flag and use the flag in set expression, something like below:

table_2:
NoConcatenate
Load id_2
, '0' as Table_Flag
, If(Len(Trim(name_qa_results_2)) = 0, Null(), name_qa_results_2) as name_qa_results_2
, If(Len(Trim(super_qa_results_2)) = 0, Null(), super_qa_results_2) as super_qa_results_2
Resident table_temp_2;

the xpression: would be: = Count({<super_qa_results_2 = {'Match'}> + <id_2 = {7}, Table_Flag={0}>} distinct id_2)

[mbespartochnyy]

It looks like you're using QlikView. The results that you're seeing in QlikView are the results that I was expecting Qlik Sense would also produce.

It looks like there might be a bug in Qlik Sense that produces wrong results.

I wonder if anyone else in the community can see the same problem in Qlik Sense that I'm seeing on my server.

[traemilietw]

On what grounds do you base that conclusion?

[mbespartochnyy]

It's not a conclusion. At this point it's only hypothesis, but it's based on the fact that same script and same expression are producing different results in QlikView vs. Qlik Sense.

[Qrishna]

i strongly believe te above code should work same both in qlikvew and sense as we are not using any functions that arent available in qlikview.

your 'dbOwner' table has only one unique id, sometime if you dont explicilty provide the info in the set expressions there are high chances of it picking up the data from recent table loaded ( i have seen in some cases) and sometimes it picks up all the available ids from all the tables when they are linked together (some other cases)

[traemilietw]

I appreciate your explanation. Thank you. I hope my question was not out of line.

[mbespartochnyy]

I agree @Qrishna. Both QlikView and Qlik Sense should produce same results in this case.

I tried rearranging the LOAD script for the 'dbOwner' table to be before the LOAD script of 'table_2' table but the expression in Qlik Sense produced the same, wrong answer.

[Qrishna]

did you try the flag method? what are the results?

[mbespartochnyy]

I did. I added the flag field to table_2:

Reloaded the app and made sure the field shows up in the data model and it did:

and I've updated the expression to also include the table_flag field:

But the expression is still returning 1.