Hi @sunil-kumar5
I would start by creating clean versions of each of the fields, where case is kept the same and some characters are replaced that could be the same but different (& and and, for instance).
You can do this with a parameterised variable, you can put as many replaces, purgechars etc. as you like in here:
set vClean = lower(replace(replace([$1], ',', ''), ' ', ''));
Load the fields with both original and clean variants:
LOAD
ID_Number,
[Customer Name],
Mobile_Number,
Address,
Email_ID,
$(vClean(Customer Name)) as [Customer Clean],
$(vClean(Mobile_Number)) as [Mobile Clean],
$(vClean(Address)) as [Address Clean],
$(vClean(Email_ID)) as [Email Clean]
FROM [... your data source ...];
You will then want to create a separate table for each of the cleaned fields which highlights any duplicates. It will not catch all things (more code into the clean variable may catch more) and there could be false positives, but it will be a good start.
The dimension in each table will be the Clean field, and the expression will be:
if(count(ID_Number) > 1, concat(distinct ID_Number, ', '), null())
This will list all of the ID numbers associated with the dupe. Clicking on the cleaned field value will select all of the rows where the original value is similar, but not exactly the same.
To catch email addresses which are the same except for the number you would need to do an extra purgechar step to remove all digits from email addresses (this seems a strange error to have though).
To compare across multiple fields at once you can add multiple dimensions to the table. This is going to provide a less sensitive test though, but would remove false positives.
Hope that helps.
Steve
