Hello,
I have 2 tables (Table1 and Table2), both queried from databases.
I need to update Table1 with Table2 by matching columns (:match).
The problem is:
- I get :match content for Table1 with a Regex from the database with (sometimes) this format: “XXXX-YY-ZZZ”.
- I get :match content for Table2 from another database with a different but consistent format: “XXXXYYZZZ”.
I can manually use If() and Subsitute() to get rid of “-“ in an extra step but I need to loop over a high number of table updates with the column format usually matching. I would like to avoid that route if possible.
I see two possible solutions:
1- Is there an elegant way within Update() that could look for the “nearest match” between columns by ignoring specific characters?
2- Is there a way to match a specific chain of character with Regex like “XXXX-YY-ZZZ” but output only “XXXXYYZZZ"? So that I can fix upstream the format of :match in table1
If you have another approach in mind, I would love to hear it.
Below an example script with the “extra” step
Thanks,
-voiz
Names Default To Here( 1 );
dt1 = New Table( "Table1",
Add Rows( 20 ),
New Column( "match", // format "XXX-YY-ZZZ"
Character,
"Nominal",
set values({"786-79-6822", "413-31-2411", "286-37-3500", "985-96-1683", "641-67-8572","537-38-5509", "136-71-5147", "965-59-8232", "141-62-8885", "212-98-2829","330-28-2219", "938-39-3349", "203-47-1149", "310-16-8252", "273-85-3049","543-87-1778", "784-24-3481", "708-81-6285", "902-33-5331", "763-59-8837"})
),
New Column( "Value1",
Character,
"Nominal",
set values ( {"1.7826391133", "3.4239116441", "0.1994181285", "3.5850992007", "1.8049403103", "2.3814269388", "2.3045256995", "3.3546657711", "2.341782487", "1.9017582694", "1.5864833118", "1.3739423649", "2.0963420644", "0.4244481763", "3.6469046306", "3.1553423936", "0.6822735481", "3.9464927437", "0.7582216617", "0.9802403664"})
)
);
dt2 = New Table( "Table2",
Add Rows( 20 ),
New Column( "match", // format "XXXYYZZZ"
Character,
"Nominal",
set values({"537385509", "708816285", "938393349", "543871778", "286373500", "902335331", "784243481", "273853049", "641678572", "203471149", "985961683", "413312411", "786796822", "965598232", "136715147", "763598837", "330282219", "212982829", "141628885", "310168252"}
)
),
New Column( "Value2",
Character,
"Nominal",
set values ( {"1.7489787", "1.763834", "3.0609319", "0.7657611", "2.0515768", "0.8648471", "1.0966909", "1.5494098", "2.7582114", "1.1778722", "2.8651535", "2.3157859", "4.3617672", "0.7137677", "1.1055379", "1.8654949", "4.0616821", "2.3122891", "1.9496742", "2.3774745"})
)
);
// I would like to remove this extra step (it doens't apply on all tables I need to loop through...
dt1 << new column ("Column 3",Character,"Nominal",formula(substitute(:match,"-","")));
// ...and make the Update() more robust with a "nearest match", not "exact "match"
dt1 << Update (
with (dt2),
match columns (Column("Column 3") == Column("match"))
)