Compare records of different tabels per variable in expression

[#1]

Hi all,

I could really use some help on the following, all help would be appreciated:

Table view:

Sheet view:

In the table view, the two tables are deliberately seperated: if the tables would be connected through a key, this question would not be needed. Both the tables 'Basic data' and 'Alternative data' are displayed for your understanding.

As you can see in the QV attached or the picture above, I am trying to compare 'Basic data' with 'Alternative data', resulting in the table 'Aim'. This table should be displayed as it is now, so my question only concerns the expression in table 'Aim':

               if([Key 2]=[Key 1] and Prop4=Prop1 and Prop5=Prop2 and Prop5=Prop3,0,1)

My question:

How can I make sure table 'Aim' only shows the records which also exist in the table 'Basic data'?

In this case all three records from 'Alternative data' are displayed, which is not the desired situation: 

- B1=A1, so must be displayed

- B2 does not exist in 'Basic data', so must not be displayed (Prop4=Prop1, Prop5=Prop2 but Prop 6 isnot Prop3)

- B3 does not exist in 'Basic data', so must not be displayed (Prop4 isnot Prop1, Prop5 isnot Prop2 and Prop 6 isnot Prop3)

Conclusion: in this case only B1 should be displayed in table 'Aim'.

Obviously, the expression as displayed does not work due to the lack of a shared key, but the lack of a shared key is part of this question.

I really hope someone can help me.

Thanks a lot for any effort in advance!

Kind regards,

Ramon Hogervorst

[Gysbert_Wassenaar · Accepted solution]

Use =if(Prop1=Prop4 and Prop2=Prop5 and Prop3=Prop6,[Key 2]) as a calculated dimension and check the option Suppress When Value Is Null.

[#3]

Fantastic, thanks a lot!