Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Detecting orphaned records

Hello !

Many times we need to check two tables, associated by a key, to check for orphaned records. That situation SHOULD NOT OCCUR on the original database but, sometimes it happens.

An example ?

Say that you have a PRODUCTS COMPOSITION table, where you list all of the parts that will build up the product. Then you have another PARTS table, item per item.

So, an error could be a PRODUCT using an non existing part or a part that is not used in any product.

I'd like to list those problems or build a table only with those records. At the end of the script I will STORE that table into a TXT and send it via email to someone else reporting the problems.

As usual I have provided a very practical sample application.

Thanks in advance !

4 Replies
boorgura
Specialist
Specialist

Adriano,

Nice question.

This is some what similar to the Set analysis in Math.

Where we have 2 sets A & B, so you need the Union of (A - B) and (B - A)

Will try to see if I can code the logic for the same.

Not applicable
Author

Hello Adriano , with the script right join the result is 59, is the value orphaned



Load

PART_CODE

Resident

PRODUCT

where

PART_CODE1 <> PART_CODE2;

Drop

table

PRODUCT;





Not applicable
Author

But we need to check (dump) PRODUCT B as well cause it points to a non existing PART (99)....

pablolabbe
Partner Ambassador
Partner Ambassador

this code creates a new table only with products without parts



PRODUCT_WO_PARTS:
NOCONCATENATE LOAD * RESIDENT PRODUCT;
JOIN LOAD PART_CODE,PART_NAME RESIDENT PARTS;
RIGHT JOIN LOAD PRODUCT_NAME, PART_CODE RESIDENT PRODUCT_WO_PARTS
WHERE ISNULL(PART_NAME);<div>
I´ve used NOCONCATENATE because PRODUCT table is already in memory. If you read directly from QVD it´s not necessary.