Topic: Validating parent/child relationship
Validating parent/child relationship
Hello DataCleaner community,
I am trying to get a hold of this useful tool and one thing that I wanted to do was validate the parent/child structure of a table. My table is something like this:
I am trying to get a hold of this useful tool and one thing that I wanted to do was validate the parent/child structure of a table. My table is something like this:
DEPARTMENTSI have tried doing this using the Javascript Evaluation validator but couldn't get it to check anything else but single rows at a time. Is it possible with DataCleaner to do this?
-----------
id (integer, pk)
name (text)
parent_id (integer or null if root)
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?
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.
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.
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.
Log in by clicking the login link at the top of the screen
Go back to forum.
