Question about how fields are linked and filling worksheet tables

[DutchArjo]

I have 2 relevant tables that are loaded in the load script. Below is how they look in the data model:

I use these to create a table to show which employes assigned to the differe groups. This works for most part, except that when an %MDW_ID is not entered in the source file (but only a name is for example), the table goes completely wrong:

as you can see, the 'Aantal'  in the left column has many time the same number while each number should be in there only once. this is how the source file looks like:

Many rows where the field "ID-nr" is empty.

under 'Name' there is one person who is in the table twice. While he isnt even member of group 2.00 (but only assigned to group 'Staf' and also to group R1.80. How is it possible his name shows up in a table which should only show members from group 2.00?

I expect it has something to do with the field %MDW_ID because that is the linked field. And this employee is also in the list without a mention of his %MDW_ID.

However, I would expect the table would simply only show all the lines that have 2.00 in the group field. But this is not how it is working apparently. Can someone explain to me how it works?

[marcus_sommer]

The impact of such issue could be numerous and quite hard to track and/or to explain. IMO it's not worth to attempt for it and/or to find any workarounds else I suggest to "fix" the root within the data-model.

This could mean to fill the missing id's from other records/sources and if it's not possible to replace the NULL with a generic default value, maybe -999. Further to check the id's from both sources against each other and populating the missing ones - by creating n extra flag-fields which contain the information if the data are origin or populated as well as valid and invalid.

The above measurements won't really improve the poor data-quality but they becomes easier to handle in the UI by using the populated information as dimension and/or selection and/or condition.