Finding non-matching entries dynamically

[Anonymous]

I have two table boxes: Table 1 and Table 2. Table 1 is the raw master data without any filters applied and won't change when different filters are applied. This is the sample data:

Table 1		Table 2
ID Number	Name	ID Number	Name
1	Tom	1	Tom
2	Dick	2	Dick
3	Harry	3	Harry
4	Ron	4	Ron
5	Paul	7	Ma
6	Jack
7	Ma

Table 2 is generated from Table 1 when some filters have been applied to the fields in Qlikview.

I want a third table to show the non-matching (omitted) entries, i.e ID Number 5 and 6 along with the names. Another thing to take care is that when I again change the filters, the contents of Table 2 will change and I would want the corresponding non-matching (omitted) entries in Table 3 to change accordingly. So if with different filters we have only 1,3,4,5,6,7 in Table 2, I want Table 3 to show ID Number 2 with the Name.

I do not know how to solve this without hard-coding the thing, so looking for any help possible. Any solution that works is fine by me, doesn't necessarily have to be set analysis or script, just needs to be flexible for all cases.

[rubenmarin]

Hi, you can add a temporary field to table 2 to check values loaded, and use this field as filter in table3, like:

Table2:

LOAD [ID Number] as chkIDNumber,

  OtherFields...

From/Resident...

Table3:

LOAD * Resident Table1 where not exists(chkIDNumber, [ID Number]);

DROP Field chkIDNumber;

[chrismarlow]

Hello,

Will out of the box set analysis do this, like the below, or is there some other complexity?

So Left box {1} ignores the selection, middle with no set (you could go with {$}) applies it, and right {1-$} gives the inverse.

Hope that helps.

Cheers,

Chris.

[Anonymous]

@chrismarlow your solution is working but now I have one more level of complexity.

How to implement this if now I change Table 2 in that it is obtained from a different source file (not Table 1,the filters and other conditions remain same) but I still want to find the omitted entries with respect to Table 1 ? Can this be done using set analysis ?

[chrismarlow]

Hi,

It depends how you model your data.

If Table1 and Table2 are similar data (say January sales & February sales) then you should probably concatenate them together, so the problem goes away.

If Table1 and Table2 are associated by a key field (or fields) then the set analysis will hold.

If Table1 and Table2 are not associated (so no joins when you look at the data model), but there is some reason to filter on values selected in Table1 that are 'the same' but not associated with a field in Table2 then set analysis plus P() and E() functions can work. That is an unusual use case though ...

Can you share a bit more detail on which of the above applies (or if something else)?

Cheers,

Chris.

[Anonymous]

It is the second case. The two tables are connected by multiple keys. I can't concatenate them due to other complications.

I'll explain the complete situation by an example.

Table 1 has 10 entries (say A1 to A10). Table 2 has 8 of these 10 entries (say A1 to A8), and after we apply some filters (the key fields) we are left with only 5 entries in Table 2 (A1 to A5).  What I wanted was the third table to show A6 to A10, the elements not in Table 2 with respect to all the elements in Table 1. So essentially the difference of the two tables, if you will.

I understand that I am changing the question from the original, but this is my end goal.

[chrismarlow]

Hmmm. Not sure I completely follow, but trying to abstract & simplify, if I do the following load;

t1:
LOAD * INLINE [
    Link, T1 Non Link
    1, A
    2, B
    3, C
    4, D
];

t2:
LOAD * INLINE [
    Link, T2 Non Link
    1, X
    2, Y
    3, Z
];

I then get the flowing behaviour from {1}, {$} and {1-$} with a filter applied, highlighted in red where because 4 does not exist as a 'Link' in t2 you would never see a record here.

Is this similar to what you are trying to do & what would you want to see in that case?

Cheers,

Chris.

[Anonymous]

Yes, this is very close to what I want. 

Instead of X,Y,Z in t2, I have A,B,C.

I want to display only D , which is not in t2 but in t1.

But you are saying that it would not be possible because the link is not there.

[chrismarlow]

Hi,

You can't display based on the contents of t2, but you can use the contents of t2 (EDIT - changing my XYZ to your ABC) to show what does not match in t1, so set analysis approach on my model is;

Only({1<[T1 Non Link]-=P({1} [T2 Non Link])>}[T1 Non Link])

But I am wondering if you would actually be better to tag up in script, as this would not seem to rely on the selections (so is less dynamic than the set analysis approach).

Cheers,

Chris.

[Anonymous]

Hi,

This gives the desired result, thanks but is less dynamic, as you say.

Will the script solution be more dynamic than this, will the contents of table 3 change dynamically if some filters are applied ? If yes, I would love to know that solution too