Label based on the date condition and categorize.

[Sarath5c8]

I have a dataset with (grandparent-parent-child relation) this together is one set. I have mulitple such sets in datasource. requirement here is Definetly DOB of GrandParent will be the least, but if child dob is greater than parent it should be label "Mismatch" and child dob need to verified with GrandParent dob and if mismatch then same label it "Mismatch" else "Correct".

There will be multiple Grandparents and their corresponding childs and parents so it should compare only with same set. Any idea how to proceed.

GrandParent	Parent	Child	DOB	Hierarchy
GPc1	Pc1	C1	1/1/2020	Child
GPc1	Pc2	C2	1/1/2000	Child
GPc1	Pc2	Pc2	1/1/2000	Parent
GPc1	null	GPc	1/1/1980	GrandParent
GPc1	Pc1	Pc1	1/1/2000	Parent
GPc1	Pc3	C3	1/1/2022	Child
GPc1	Pc3	Pc3	4/5/2001	Parent

[Sarath5c8]

GPc1 - {Pc1,c1} -{Pc2,c2} - {Pc3,c3}

logic: Date of birth of GPc1 > Pc1/Pc2/Pc3 > c1/c2/c3  - Valid scenario 

but suppose Dob of c2>Pc2 then it should be labelled as "Mismatch"

and it only [GPc1 - {Pc1,c1} -{Pc2,c2} - {Pc3,c3}] this is pair and should not be compared with other set like c2 with GPc10 because GPc10 is grandparent of some other family.

[v-stephen-msft]

Hi @Sarath5c8 ,

Sorry, not very clear. Is it possible to provide the expected results for the sample data?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.