Hi
I am trying to create a page which will show similar records by match percentage of the current record i am viewing.
Example;
In dataverse we have a table called 'USER DATA' with the following fields: [NAME, REF, APP_NO, APP_DATE, EMAIL]
NAME | REF | APP_NO | APP_DATE | |
JOHN SMITH | 12345 | APP12345 | 12/12/22 | J.SMITH@MSN.COM |
J SMITH | 12344 | APP12345 | 12-12-2022 | J.SMITH@MSN.COM |
JOHN JOE SMITH | 12345 | APP12344 | 13-12-22 | J.SMITH@MSN.COM |
JJ SMITH | 12333 | APP12345 | 12-12-2022 | J.SMITH@MSN.CO |
BRUCE WAYNE | 43455 | APP5454 | 2-2-2022 | BRUCE_WAYNE@GMAIL.COM |
B WAYNE | 43455 | APP54545 | 2-2-2022 | BRUCEWAYNE@GMAIL.COM |
TOM RIDDLER | 55334 | APP123444 | 2-5-2021 | T.RIDDLER@MSN.COM |
JACK SPARROW | 12345 | APP54532 | 2-5-2021 | J.SPARROW@GMAIL.COM |
MICHAEL JORDAN | 4445555 | APP4443333 | 12-3-2021 | MJORDAN@HOTMAIL.COM |
As you can see the 'J smiths' there are duplicates and so it 'Bruce Waynes' as they have similar 'Ref, App_no,App_date or Email' with some typos in the email or ref etc.
So in a canvas app if i am viewing the first Item:
JOHN SMITH | 12345 | APP12345 | 12/12/22 | J.SMITH@MSN.COM |
It should also give me similar records with match rate/percentage on string match(reduce to all lower case then do string match) eg:
NAME | REF | APP_NO | APP_DATE | MATCH% | |
j smith
Match: 82.35% | 12344
Match: 80% | app12345
Match: 100% | 12/12/22
Match: 100% |
Match: 100% | 92.47%
Calculations= (82.35+80+100+100+100 )/5 |
john joe smith
Match: 83.33% | 12345
Match: 100% | app12344
Match: 87.5%
| 13-12-22
Match: 62.5% |
Match: 100% | 86.66% |
jj smith
MATCH: 77.78% | 12333
MATCH: 60% | app12345
MATCH: 60% | 12-12-2022
MATCH: 66.67% |
MATCH: 96.55% | 72.2% |
Which can be shown on a new tab titled 'Similar records'
Is there a way to do this or a better way to do this as theres going be to a big data involved over 1 million rows?
Thanks
Zizu
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2