Lookup help

Question

I am creating a repetitive flow which is comparing the data within our system to files received from the customer. These files are very large (~40,000 lines).

I need to compare the children's information but some families may have more than one child. Our system has an unique identifier for each child but the customers file doesn't. Both the system and customer's file has an identifier for the entire family.

I need to find out if there are new children, children no longer on the plan, if the child's first name, last name, DOB, etc.. is different between our system and the customers file.

If I concatenate the data by the parent's identifier and join by the concatenation the children are not matched/lined up properly (as some families have more than one child). I want to be specific with the required action, example; new child, remove child, name error, etc. so if I concatenate the first name with the parent's identifier it will not be specific enough, it could be a spelling error, new child, etc.

Any ideas?

grzegorzs · Answer

Dzień dobry Panu Rafałowi :)

Ciężko Cię znaleźć w świecie. Jak żyjecie?

Pozdrawiam,

Grzesiek

Emil_Kos · Answer

Hi @reginawhelan,

I have prepared a solution for you. It should identify positions that are incorrect. The same for new children or children that aren't longer in the list.

It will currently not work for twins (as if two children in one family will have the same date of birth if will create a duplicate).

Do you have situations like this?

Join - Parent Child.yxzp

reginawhelan · Answer

@rafalolbert

Hi, I don't see how this process could work for a repetitive flow (executed every week) with over 40,000 rows in each file. I attached a data sample similar to the files (just much smaller). My issue is not with the parents, it's with the children. I need to compare the data but can't seem to join them properly.

Thanks,

Regina