The scenario involves two address/street lists that I’m trying to compare using the interval merge action to identify where mismatches exist between the two tables (great feature). It would be very useful if there was a way to create a mapping/renaming table where we could apply renaming of a value in one table to match the value in the other. I’m thinking of using the replace action on the field being used to link the two tables but then how do I obtain a list of all the replacements made as I need to know both the original source value and the replacement value. Do I create a copy of the column and then later derive a table that has distinct values where original field name != replacement field name. Is there a better way to approach this in EM?
PS, have been doing data analysis/cleansing for a long time and haven’t found a tool that comes close to doing what EM does - so many great actions that save a ton of time. The swiss army knife that every data analyst needs smile:
Realizing that this isn’t quite going to work as many replacements I do will affect all the rows in the table not just the selected row. For example if a cell has an empty/blank value there is no way to edit/correct that value.
One idea, similar to Create List action add a Create Table action that would be editable and/or extend the List feature to support editing of values (multi-column). Then on the right click cell/row action could be an option to add value to table. This other table would link back to the maintain table with a merge action.
I’m assuming that most users need to correct bad or missing data so having some kind of integrated feature for this would be a nice enhancements.
Short-term the workaround is to maintain an open Excel spreadsheet where I can copy and paste values to. I provide a unique record identifier if I want the change applied to one row or omitted if applies to all rows). Then as I add the values to the external spreadsheet I just reload it as needed. Is there a better or more efficient way to handle this?
Point is do I need to really open an external program/spreadsheet or can EM at some point support this internally?
I do a similar concept with Both flat files and DB Tables. It involves identifying the bad data and doing update statements on the tables if its SQL.
If the data is a flat file or excel I will import, run my changes in EM export it with a new name, delete the old file via Actions, then rename the new file to the old name.
smile: