Topic: Validating parent/child relationship

Forgot to mention exactly what I wanted to validate! I want to check that for each parent_id there should be a corresponding id value in some other row. Except if the parent_id is null.

I think you can solve this using a database dictionary (for the ID column) and a dictionary matcher profile (for the PARENT_ID column) but I don't know about the nulls/roots of your hierarchy.

Hmm I can't seem to get this to work. I succesfully created a dictionary for my id-column but I can't get the matching up and running.

One way is by using the validation rule "Dictionary lookup" and, for some reason I can't explain, none of the parent_id's are correctly looked up according to the id-column (they are all stated as "not validated" even though I can manually validate them all, except the null's).

Another way is using the profile "Dictionary matcher" but I can't select the parent_id column in the "Apply to columns" selection. This seems like a bug to me?

Hi Franklin,

The dictionary functionality was originally not meant to be used for anything but strings and this is why the "Apply to columns" selection is grayed out for your ID-columns (which I'm guessing are INTEGER columns).

I do think this is kinda buggy though so I will add a ticket for it for DataCleaner 1.5 so you can use the dictionaries as a number-matching mechanism.

For the record (so you can follow along), the bug is registered as Ticket #260

Progress on this topic? I would be interested to but my Java skills are pretty elementary and I wasn't able to figure it out when looking at the code.

Hi franklin,

With the just-released datacleaner 1.5 it is now possible to use number-columns with dictionaries. Both to look up using numbers and to store dictionaries consisting of numbers.