Updating Fields in One Input from Another

[ChristineB]

Scenario:

You monitor the mileage of multiple trucks as they deliver shipments over the course of a week and record additional information regarding each truck in a file (Truck Metrics).  Each truck’s cumulative mileage per day is recorded in a separate file (Truck Mileage).  Your goal is to update the mileage-related fields in Truck Metrics with the values recorded in Truck Mileage.  Note: today is Tuesday so only fields for Monday and Tuesday will be updated in in the Truck Metrics file.      

Methods: 

• Manually Select Fields to Update

Whether the data is uniquely identified by a Truck Number (or Record ID) or identically ordered in both files, data from Truck Metrics (the table to be updated) and Truck Mileage (the data used for updating) can be Joined together.  Then, using the Select functionality within the Join tool, I can manually choose the fields from Truck Mileage that I want to use to replace the fields that need to be updated (Figure 1).    

Figure 1: Fields from Truck Mileage (Yellow) replace fields from Truck Metrics (Blue).  Note that fields in yellow are selected while fields in blue have been deselected.  Fields that need to be included from Truck Metrics (Red) remain selected. 

Fantastic!  A simple, straightforward way to update fields!  But, as any analyst knows, working with data is rarely simple or straightforward.  What if you’re dealing with 20 fields that need to be updated, not just 2?  In that case, manually selecting fields to update is not only tedious but also error-prone.  For these types of situations, I recommend a process that allows for a more dynamic approach.    

• 'Verticalize' the Data to Dynamically Update Fields

Transposing, or ‘verticalizing’ data, allows for a more dynamic workflow when you have unknowns in your processing.  In a scenario such as this one, you may have an unknown or changing number of fields that will need be updated in Truck Metrics.  Using this approach, we’ll first Transpose both data sets to configure the Field Name and its associated value in a single row (Figure 2). 

Figure 2: The data is transposed from Truck Mileage and Truck Metrics.  The highlighted fields in Truck Metics indicates which fields will be updated, as the same field exists in Truck Mileage.

 

Then, we’ll Join our datasets based on two fields: Truck Number AND Name.  This ensures that fields in Truck Mileage will match to the correct fields in Truck Metrics, assuming the fields names in both tables are named in the same way.  The only selecting we’ll have to do is to make sure all fields from the Right data source (in this case, Truck Metrics) are deselected (Figure 3).  This allows that, in the situation of matched Truck Numbers and field names, the updated values will be used.

Figure 3: The Joined fields indicate the fields that exist in both Truck Mileage and Truck Metrics.  Fields in yellow (Truck Mileage) are selected to reflect updated values in downstream tools.  Fields in blue (Truck Metrics) are deselected. 

Note that any unmatched fields have fallen out of the Right side of the Join:

To add them back into the data stream, simply Union the Center and Right Joins together, setting the tool to “Auto Configure by Name”.  Then, to rearrange the data into its original format, use the Cross Tab tool (Figure 4).  And, voila!  Your data is updated!

Figure 4: The updated fields are highlighted in yellow.  All other fields originally included in Truck Metrics are included the in the final results as well.