Forum Discussion
Report discrepancies in data flow between multiple systems
Hi All,
I need help in reporting discrepancies in the data that is between different systems.
So, for example, I have a source system (Table A) which has information as follows.
Name | Id | Course | |
Tom | 123 | CS_1 | |
Mini | 124 | CS_2 | |
Arya | 125 | CS_1 |
An intermediate system (Table B)
Name | Id | Course | |
Tom | 123 | CS_1 | |
Mini | 124 | CS_1 | |
Arya | 125 | CS_1 |
And a destination System (Table C)
Name | Id | Course | |
Tom | 123 | CS_1 | |
Mini | 124 | CS_1 | |
Arya | 125 |
| CS_1 |
As you can see that the Course for Mini has flown down incorrectly from Table B and Arya is missing the email in Table C. I want to be able to report these mismatches in the form of a visual (Pie chart ) which shows red as the mismatch count and when I click on it I can drill down to seeing what are the values that don’t match.
Do you have any suggestions or best practices to report this incorrect data flow?
Thanks,
Chris
4 Replies
Anonymous
My solution can't 100% meet your request.
Use lookup value to get email and course value from TA and TB
email from TB = LOOKUPVALUE('TB'[Email],TB[Name],TC[Name])Then compare if TC = TB and TC = TA
iscorrect = if(TC[Email]=TC[email from TA]&&TC[Email]=TC[email from TB]&&TC[Course]=TC[course from TA]&&TC[Course]=TC[course from TB],"Correct", "Wrong")Then create a pie chart.
Legend: iscorrect
Details: name
Values: count of iscorrect
Hope this is helpful.
Anonymous it can be super easy if you clarify the following points:
- ID column is unique in all these 3 Tables?
- what are we checking for incorrect/mismatch, email only?
- Are we comparing the value of only Table C with Table A and Table B? What happens if there is a mismatch between Table A & Table B or that will never happen? If there will be never a mismatch between Table A & Table B, it means we can compare Table C with any one of Table A & Table B.
Hi parry2k ,
Here are the answers to your questions:
1. Yes ID column is unique in all 3 tables.
2. Nope, mismatch could be in any of the fields email, course, name ect.
3. There is a mismatch between Table A and Table B in this case.
I basically want to report anything that has a mismatch when data flows between the three tables. It would be great if the able numbers are scaleable.
Hope that answers your question.
Kind Regards,
Chris
LOOKUPVALUE is a great approach. Another approach is to use TREATAS where you impose the parameters of the desired pattern onto the table to be tested, and the gaps will then be shown (or rather you will see stuff missing) as a result.
