Forum Discussion
Comparing info between rows and columns
Hello,
I have a very complicated table that I'm trying to sort -find errors.
I've done multiple attempts to do this, and I always feel like I'm doing this too complicated. Plus I built it to find problems I saw, I don’t know if there are others.
So, I'm asking for help.
This is a sample of one person out of over a 1000. The number of contracts and locations can vary.
ID | date | Status | Contract | Located |
1 | 30/06/2003 | open | B | There |
1 | 01/10/2009 | Close | A | There |
1 | 01/10/2020 | open | B | Here |
1 | 01/10/2009 | close | A | Here |
No matter how many locations a person has the contracts are always equal between them.
Mistakes in this example alone:
Located "there"- contract B start date is before contract A
And Contract B has different dates in "Here" and "There".
I have over 1000 people, over 100 different units and 10+ contracts.
My latest attempt was to compare rows by ID (creating 16 comparisons in this case)
And creating different "if" columns.
The original data is an excel file I get sent periodically. And the problem shown here is new even though the last change for this person was done in 2020.
I have so many other problems with the data, but this is the one I'm finding the hardest to tackle.
If what I want is not possible and or doesn't make sense, then I'm sorry.
Thank you.
2 Replies
What is your expected output? What are we doing to identify what is an error? What are all the types of errors that could occur?
The expected output is the same file with the errors marked. So I can send it back and ask them to fix it.
types of error that can happen are:
A person can be connected to either one or multiple locations but they always have the same contracts.
Only one contract can be in open status at a time, and it has to be open in all locations.
A contract can only appear once per location(I have a case that someon has contract A twice- once open, once close)
The date for a contract is the same in all locations.
an open contract can't have an earlier date then a closed one.
"null" values are an error.
Contracts have order- Contract B can only happen when contract A closes, not the other way around.
That's all that I can think of now.
What do you mean by " What are we doing to identify what is an error?"
Thank you
