Finding out Orphaned Records

[#1]

Hello !

Very often I need to check the integrity of the loaded data in order to search for orphaned records.

Here's an example : there's a a SALES table, where we have a PRODUCT NAME and then we have another PRODUCTS TABLE where of course we also have the PRODUCT NAME (KEY).

There may be records on the SALES table mentioning nonexisting PRODUCTS. That is, I may have a sales for a PRODUCT ITEM that is not on the PRODUCTS TABLE.

It should not be like that of course but, it happens.It's just an example. The real situation is way more complex.

Anyway, my point is : WHICH IS THE BEST WAY (more efficient/fastest way) to find out those records ?

I've done a TABLE BOX where I can see the nil entries. But a TABLE BOX for a HUGE DATABASE is very cumbersome.

Ideally I'd like to detect that situation upon script load and MAYBE, report that somehow or send an email. That would be great. But at least if I can easily detect that would be fine.

Have prepared a tiny QVW to show the problem.

Thanks in advance 4 your support !

[johnw]

I'd probably turn the PRODUCTS table into a MAPPING LOAD, then put the description on the SALES table with a default value like applymap('PRODUCTS',"PRODUCT_NAME",'INVALID') as "DESCRIPTION", and then select INVALID. Or if you want the table on a permanent basis, make it as a straight table with expression DESCRIPTION='INVALID', then hide that column. See attached.

[#3]

I see !

Thanks !

For this particular case, it works.

But maybe we could have a more generalized approach, for any situation !

Shall we hear for more suggestions ?

Cause since my data is coming from non-reliable sources, I may have this orphaned key problem elsewhere.

So I'd like a more generalized and more automatic detection.

But let's listen some more, ok ?

Thank you again !