Joining transactions to master data

Deprecated

Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap applications I had created. I didn’t remove the entire topic as I thought some of the code may still be useful with some modifications. 

This parameter block is handled by cDataSet classes and provides a mapping of fields that are required on a Google Map or its visualizations and where to find them in Excel WorkBook.

For this example, I had created a Concerts/Venues example. The complete workbook (googlemapping.xlsm) can be downloaded and the example Parameter WorkSheet is called VenuesParameters. 

Master data joins

Although strictly speaking this is a more general topic, not completely Mapping related, in the real world a VizMap application will be dealing with static data in the form of a master sheet and lookup sheet to translate codes into text, all of which are applied to a transaction sheet containing the list of associations that make up the data. This is how a normal database application would work. The ‘join’ of all these components would be the input data to the mapping process.  Since we are using cDataSet behind the scenes, we already have most of the tools needed to do this kind of matching with little effort.  Using the same parameter sheet that contains the VizMap Dictionary we can add some entries that describe how to join the data from the contributing sheets.

The transactions

Here are the transactions for the Concerts/Venues example.

Before that can be plotted we need to look up information about the Venue and the Artist. Which columns to copy over is defined in the ‘clone’ parameter block. You can clone from as many of these inputs as you like

The definition of the source, and how to join with the transactions is in the Name parameter block.  Note that Join and Transactions have no Join parameter. This is because Transactions is the source data worksheet , and Join is the worksheet to populate with the result. The Join columns need to exist in both the transaction and lookup sheet – so for example, the  Artist ID column must exist in both the Transactions and Artists sheet.

The Join

 Once joined, the VenueMapping sheet (the input to VizMap) looks like this.

Combining GeoCoding, Joining and VizMap

Now we have all the components to GeoCode, manupulate the data, and create a VizMap application. Depending on your particular requirements you may not need to do this every time you regenerate the application but here is the VBA for the combined end to end process where paramName will be the name of your parameter sheet. 

Private Sub endToEnd(paramName As String)
    ' geocode the master
    googleMappingExample paramName
    ' join to transactions
    transactionJoinExample paramName
    ' create tabbed/viz/mapped app
    googleMarkingViz paramName
End Sub

Advanced Joins

There may be times that you need to join to a field with a different name, or even copy over and change a field name.  This is accomplished as follows.

Note that a new master called Managers is created, accessing the same worksheet as Staff. The staff transactions dataset which is driving this, has both Employee ID and Manager ID. This syntax, Employee ID=Manager ID, is instructing the Join operation that for the Managers Master Dataset, the join needs to be between Manager ID in the Transactions sheet and Employee ID in the Staff Sheet.

The whole purpose of joining like this is to be able to use the same master dataset for multiple purposes. This means that we will also need to clone the fields and call them something different in the joined dataset, as per the two lines that are cloned from the master dataset below.

Note for organization charts

When creating organization charts, it is normal to not have a match for the person at the top of the organization. In the example above, employee ID 10, has a manger of 0. Manager ID 0 does not exist, so the join process will complain about the mismatch, but will complete and will not affect the generated application.  Questions, feedback and VizMap applications you have generated can be submitted for loading to this site via our forum.  The next step is to define the VizMap Application Filename which identifies the filename to create.