Fuzzy search for enterprise grade Apps

The language is certainly accessible... but it feels any sophisticated routines are held together by sticky tape, as demonstrated by your own examples of manipulating arrays!!

Anyway, for the benefit of everyone - a simple-to-use formula (with fiendishly complex script) to calculate the Levenshtein Ratio between two strings (ranges from 0-100). 

Usage example (assuming a table called Companies):

let matchCompany := “JP Morgan”;

let allCompanies := select Companies;

for c in allCompanies do

  if fuzzyRatio(matchCompany, c.“Company Name) > 0.75 then

    c

  end;

end;

The script (requires a number of helper/utility functions):

”// utility function which turns a comma-separated string back into a matrix (array of arrays)“;
function repackMatrix(array : text,width : number) do
let width := width;
let textToArray := split(array, ”,“);
let textToArray := for i in textToArray do
number(i)
end;
let rows := count(textToArray) / width;
let matrix := for i in range(0, rows) do
slice(textToArray, i * width, i * width + width)
end;
matrix
end;
”// reads single value in a matrix based on array coordinates, with top left 0,0“;
function readMatrix(matrix : text,width : number,row : number,col : number) do
let width := width + 1;
let findPosition := row * width + col;
let textToArray := split(matrix, ”,“);
let valueFound := item(textToArray, findPosition);
number(valueFound)
end;
”// replaces value in a matrix (array of arrays), which is entered as comma-separated string“;
function updateMatrix(matrix : text,width : number,row : number,col : number,value : number) do
let width := width + 1;
let findPosition := row * width + col;
let textToArray := split(matrix, ”,“);
let newArray := for i in range(0, count(textToArray)) do
if i = findPosition then
value
else
number(item(textToArray, i))
end
end;
let repackedMatrix := repackMatrix(text(newArray), width);
repackedMatrix
end;
”// calculates the levenshtein ratio between two strings“;
”// see https://www.datacamp.com/community/tutorials/fuzzy-string-python for details“;
function fuzzyRatio(wordOne : text,wordTwo : text) do
let s := wordOne;
let t := wordTwo;
let rows := length(s) + 1;
let cols := length(t) + 1;
”// initialises a matrix of zeros“;
let distance := for i in range(0, rows) do
for j in range(0, cols) do
0
end
end;
”// populates matrix of zeros with the indeces of each character of both strongs“;
let indexed := for i in range(0, rows) do
for k in range(0, cols) do
if k = 0 then
i
else
if i = 0 then k else 0 end
end
end
end;
”// iterate over the matrix to compute the cost of edits/substitutions“;
let computed := for col in range(1, cols) do
for row in range(1, rows) do
let cost := 0;
if item(s, row - 1) = item(t, col - 1) then
cost := 0
else
cost := 2
end;
let updateValue := min(readMatrix(text(indexed), cols - 1, row - 1, col) + 1, readMatrix(text(indexed), cols - 1, row, col - 1) + 1, readMatrix(text(indexed), cols - 1, row - 1, col - 1) + cost);
indexed := updateMatrix(text(indexed), cols - 1, row, col, updateValue)
end
end;
let edits := readMatrix(text(indexed), cols - 1, rows - 1, cols - 1);
”// calculates the levenshtein ratio“;
let ratio := (length(s) + length(t) - edits) / (length(s) + length(t));
ratio
end