System and method for automatic reconciliation of data
View Patent ↗A reconciliation engine and data reconciliation techniques are disclosed that partially use machine learning and natural language processing techniques to overcome challenges associated with traditional manual reconciliation engines and data reconciliation techniques without compromising the accuracy and handle the complexity of the features. In one aspect, a computer-implemented method is provided that includes: accessing a source and target table, performing intra-reconciliation on the source table, grouping the source and target table based on system configurations, reconciling data is iterated over each group, where the reconciling data includes: finding, using a machine learning model, a closest match for each group, and outputting the closest match for each group.
1 . A computer-implemented method for automated reconciliation of data, the computer-implemented method comprising:
accessing, by one or more processors, source table data of a source table and target table data of a target table, the source table and the target table being stored in a database;
performing, by the one or more processors, intra-reconciliation on source records of the source table, the intra-reconciliation including identifying in the source table and removing duplicate source records, and combining source records with common field values in at least one field by summing amounts of the source records having the common field values, wherein the at least one field is specified by a user-configurable key in a system configuration;
grouping, by the one or more processors, the source table data and the target table data into groups based on a key value specified in the system configuration, the key value being a title of a column for a respective field, wherein the grouping reduces a search space for reconciliation;
reconciling, by the one or more processors, data for each group, wherein the reconciling is performed by iterating over each group and comprises performing for each group:
determining that a value of at least one column of the target table has a null value,
determining that a multiple column mapping is on, as the system configuration, and
in response to the multiple column mapping being on, performing for each column of the target table having the null value:
checking a data type,
determining whether the data type is a string type,
(1) when the data type is the string type:
identifying N-grams of a source table record value,
forming N-gram pairs from a target table record description,
calculating a Levenshtein distance between the N-grams of the source table record value and each pair of the N-gram pairs from the target table record description,
fetching a maximum score derived from the Levenshtein distances, and
assigning, as a value to the column, the N-gram pair having the maximum score,
(2) when the data type is not the string type:
creating a string from a source table record,
subtracting the created string from a target table description,
dropping all alphabets, and
typecasting the string to an integer, and assigning to the column,
wherein (1) and (2) are performed to obtain an updated target table having no null values;
determining, using a machine learning model, one or more closest matches for each group using the updated target table;
identifying, by the one or more processors, any partial payments in the target table for each group using the one or more closest matches, summing amounts of the partial payments to obtain a summed amount for each group, and checking whether the summed amount is equal to an amount of a corresponding source table record within a predefined tolerance; and
outputting, by the one or more processors for each group, the one or more closest matches for each source record and a result of checking whether the summed amount is equal to the amount of the source table record within the predefined tolerance.
2 . The computer-implemented method of claim 1 , wherein, when the summed amount is equal to the amount of the source table record within the predefined tolerance, the summed amount is considered as a partial payment or aggregation.
3 . The computer-implemented method of claim 2 , wherein the machine learning model uses a k-nearest neighbors algorithm to find similarities between new record data in the source table and existing available records in the target table.
4 . The computer-implemented method of claim 1 , wherein the determining the one or more closest matches further comprises:
generating a scoring vector for a single record, finding a mean of all feature values to get a similarity index, sorting mean scores corresponding to each record, and fetching best matches based on top relative score.
5 . The computer-implemented method of claim 1 , wherein, when the data type is not the string type, the method further comprises:
calculating a relative difference; and
inputting the relative difference into the machine learning model to find a closest match.
6 . The computer-implemented method of claim 1 , wherein the outputting further comprises displaying in a graphical user interface the one or more closest matches for each group and the result of checking whether the summed amount is equal to the amount of the source table record for each group.
7 . A system comprising:
one or more processors; and
one or more computer-readable media storing instructions which, when executed by the one or more processors, cause the system to perform operations including:
accessing source table data of a source table and target table data of a target table, the source table and the target table being stored in a database;
performing intra-reconciliation on source records of the source table, the intra-reconciliation including identifying in the source table and removing duplicate source records, and combining source records with common field values in at least one field by summing amounts of the source records having the common field values, wherein the at least one field is specified by a user-configurable key in a system configuration;
grouping the source table data and the target table data into groups based on a key value specified in the system configuration, the key value being a title of a column for a respective field, wherein the grouping reduces a search space for reconciliation;
reconciling data for each group, wherein the reconciling is performed by iterating over each group and includes performing for each group:
determining that a value of at least one column of the target table has a null value,
determining that a multiple column mapping is on, as the system configuration, and
in response to the multiple column mapping being on, performing for each column of the target table having the null value:
checking a data type,
determining whether the data type is a string type,
(1) when the data type is the string type:
identifying N-grams of a source table record value,
forming N-gram pairs from a target table record description,
calculating a Levenshtein distance between the N-grams of the source table record value and each pair of the N-gram pairs from the target table record description,
fetching a maximum score derived from the Levenshtein distances, and
assigning, as a value to the column, the N-gram pair having the maximum score,
(2) when the data type is not the string type:
creating a string from a source table record,
subtracting the created string from a target table description,
dropping all alphabets, and
typecasting the string to an integer, and assigning to the column,
wherein (1) and (2) are performed to obtain an updated target table having no null values;
determining, using a machine learning model, one or more closest matches for each group using the updated target table;
identifying any partial payments in the target table for each group using the one or more closest matches, summing amounts of the partial payments to obtain a summed amount for each group, and checking whether the summed amount is equal to an amount of a corresponding source table record within a predefined tolerance; and
outputting, for each group, the one or more closest matches for each source record and a result of checking whether the summed amount is equal to the amount of the source table record within the predefined tolerance.
8 . The system of claim 7 , wherein, when the summed amount is equal to the amount of the source table record within the predefined tolerance, the summed amount is considered as a partial payment or aggregation.
9 . The system of claim 8 , wherein the machine learning model uses a k-nearest neighbors algorithm to find similarities between new record data in the source table and existing available records in the target table.
10 . The system of claim 7 , wherein the determining the one or more closest matches further includes:
generating a scoring vector for a single record, finding a mean of all feature values to get a similarity index, sorting mean scores corresponding to each record, and fetching best matches based on top relative score.
11 . The system of claim 7 , wherein when the data type is not the string type, the operations further include:
calculating a relative difference; and
inputting the relative difference into the machine learning model to find a closest match.
12 . The system of claim 7 , wherein the outputting further includes:
displaying in a graphical user interface the one or more closest matches for each group and the result of checking whether the summed amount is equal to the amount of the source table record for each group.
13 . One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause a system to perform operations including:
accessing source table data of a source table and target table data of a target table, the source table and the target table being stored in a database;
performing intra-reconciliation on source records of the source table, the intra-reconciliation including identifying in the source table and removing duplicate source records, and combining source records with common field values in at least one field by summing amounts of the source records having the common field values, wherein the at least one field is specified by a user-configurable key in a system configuration;
grouping the source table data and the target table data into groups based on a key value specified in the system configuration, the key value being a title of a column for a respective field, wherein the grouping reduces a search space for reconciliation;
reconciling data for each group, wherein the reconciling is performed by iterating over each group and includes performing for each group:
determining that a value of at least one column of the target table has a null value,
determining that a multiple column mapping is on, as the system configuration, and
in response to the multiple column mapping being on, performing for each column of the target table having the null value:
checking a data type,
determining whether the data type is a string type,
(1) when the data type is the string type:
identifying N-grams of a source table record value,
forming N-gram pairs from a target table record description,
calculating a Levenshtein distance between the N-grams of the source table record value and each pair of the N-gram pairs from the target table record description,
fetching a maximum score derived from the Levenshtein distances, and
assigning, as a value to the column, the N-gram pair having the maximum score,
(2) when the data type is not the string type:
creating a string from a source table record,
subtracting the created string from a target table description,
dropping all alphabets, and
typecasting the string to an integer, and assigning to the column,
wherein (1) and (2) are performed to obtain an updated target table having no null values;
determining, using a machine learning model, one or more closest matches for each group; using the updated target table;
identifying any partial payments in the target table for each group using the one or more closest matches, summing amounts of the partial payments to obtain a summed amount for each group, and checking whether the summed amount is equal to an amount of a corresponding source table record within a predefined tolerance; and
outputting, for each group, the one or more closest matches for each source record and a result of checking whether the summed amount is equal to the amount of the source table record within the predefined tolerance.
14 . The one or more non-transitory computer-readable media of claim 13 , wherein, when the summed amount is equal to the amount of the source table record within the predefined tolerance, the summed amount is considered as a partial payment or aggregation.
15 . The one or more non-transitory computer-readable media of claim 14 , wherein the machine learning model uses a k-nearest neighbors algorithm to find similarities between new record data in the source table and existing available records in the target table.
16 . The one or more non-transitory computer-readable media of claim 13 , wherein the determining the one or more closest matches further includes:
generating a scoring vector for a single record, finding a mean of all feature values to get a similarity index, sorting mean scores corresponding to each record, and fetching best matches based on top relative score.
17 . The one or more non-transitory computer-readable media of claim 13 , wherein, when the data type is not the string type, the operations further include:
calculating a relative difference; and
inputting the relative difference into the machine learning model to find a closest match.