Hello Everyone,
I'm trying to figure out why it is that my running-count is displaying too high of a number. I'm putting together a list of employees and their emergency contacts phone numbers.
The query calculation looks like this:
running-count ([Phone Data X].[Emergency Contacts Reference Data Key] for [Phone Data X].[Contact Name (First MI Last Suffix)])
The Emergency Contacts Reference Data Key is the Employee Number and the Contact Name is the Emergency Contact's name. So the product of this is to get a running count of how many phone numbers each of the employee's contacts have.
The problem is that it's default to 2 for a specific employee. It should show a record for 1 and 2. But because it only sees 2 it kicks it out because there was no 1 beforehand. I can say there are multiple joins along the way, but it seems when I join these two tables that were the problem begins. There are thousands of other records that are doing just as planned. It's just about a dozen or so that this problem arises.
We are trying to migrate our data into another system.
This has been resolved. The problem was two employees had a mother by the same name even though they weren't related. I had to change how I calculated my running count to include address because they lived it two different homes.
I'm not sure how to resolve this ticket.
It's not a ticket. It's a forum post.
You should always use the employee ID (some alphanumeric value that HR uses to ensure they are working with the correct employee data) NOT employee name.
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
Plus, what if those two people had the same address?
I did that. See my original post. The issue has been resolved.
Your response to your original post indicates you didn't.
In your original post you said you used
running-count ([Phone Data X].[Emergency Contacts Reference Data Key] for [Phone Data X].[Contact Name (First MI Last Suffix)])
Your response indicates you switched to using
running-count ([Phone Data X].[Emergency Contacts Reference Data Key] for [Phone Data X].[Contact Name (First MI Last Suffix)], [Phone Data X].[Home Address])
I'm saying you should probably use something like
running-count (1 for [Phone Data X].[Emergency Contacts Reference Data Key])