Find different data from two related tables

[schmimla21]

Hello,

I`m working on a Qlik View App in which I want to gather maintenance data from laboratory devices. I extract the data from database tables which are filled by users. Therefore, the data from two different tables may differ from each other. Here is an example of what I mean:

There are Devices (table "DEVICES") with IDs and there can be internal and/or external maintenance dates ("tests") concerning these devices (you could say that this is the parent table):

Furthermore there are is a table ("DEVICE_TESTS") which includes the maintenance dates respectively the test dates (this would be the child table).

What I want to achieve now is to find the Devices where "NEXT_TEST_DATE_INTERNAL" or "NEXT_TEST_DATE_EXTERNAL" from table DEVICES is not equal to "NEXT_TEST_DATE" depending on whether it is an external or internal test. For example: If in the screenshots above "NEXT_TEST_DATE_EXTERNAL" would be 09.11.2022,  I would like to have a simple listbox in which the affected device is listed.

Of course I know, that this data structure does not fit to the logic of a relational database. But let`s assume that this structure is necessary in this case.

I hope  that this explanation of the problem is understandable. Thank you very much in advance for any help!

[marcus_sommer · Accepted solution]

Just with these information it's difficult to deduce your data-structure and what do you want to achieve with them. Personally I would tend to merge all the fact-data into a single table - mainly by concatenating the data. This means for example that only a single date-field would be there and another field holding the information of intern or extern or planned (I assume that the other date stands for it).

With an appropriate sorting within a resident-load you could check and access with interrecord-functions previously loaded records and creating with them a difference between the dates and maybe also a flag from it. Another approach to get such flag may be to join or map such planned date to the devices and then creating the flag with it.

- Marcus