Hello experts,
I am making a report that shows who in my company has sat a specific training course. I have to produce a list and a count of all users who have passed, failed, or not yet done the course. So:
In data provider 1 (Excel), I have a list of all the people who have attended the course; fields: usernames, pass/fail status, and exam mark against each.
In data provider 2 (Universe), I have a much bigger list of usernames - all those in the company, and which team they work in.
My aim is to show, for each company username, their team, and their pass fail status; where there is no exam status for that person - i.e. when they exist in the big company list but not in the course attended list - then the cell should be null for them.
My problem is that when I merge username from dp1 and dp2 it counts twice the usernames that are in both - so the count is off. E.g. user '123' is in Team A. This username is present in dp1 and dp2. In dp1 they have exam status of Pass. So when I count how many people passed in Team A, user '123' is counted twice, when it should only be counted once. How do I get the count to exclude duplicate rows, but keep the exam status?
Thanks :)