JoinViews()

Summary

Creates a joined view by linking fields in two existing views.

 

Syntax

new_view_name = JoinViews(string joined_view_name, string left_field_spec, string right_field_spec, array options)

 

Argument	Contents
joined_view_name	A proposed name for the new joined view; can be null
left_field_spec	The view on the left side of the join and its linking field
right_field_spec	The view on the right  side of the join and its linking field

 

Option	Type	Contents
A	N/A	A, for Aggregation, specifies that for each left record, the joined view aggregates all right records with a matching link field value, computing the appropriate aggregations for each field in the right view
A? or A?0	N/A	A? causes the program to take a cursory scan of the right link field values to search for repeated non-missing values. If any are found, it is treated as option A (above), otherwise it is ignored. A? lets the program make an automatic decision as to whether the right records should be aggregated, but is not precise; it does not scan all right records. A?0 does the same thing, but columns with zero values are treated as having missing values and are therefore skipped
I	N/A	I, for Inner join, indicates that the joined view does not include left records for which there are no matching right records. As a result, the joined view may have fewer records than the left view
N	N/A	N, for Near-matches, causes any left record that has no exact matching right record to match a right record with the next higher right link field value
O	N/A	O, for One-to-many, indicates that the joined view repeats left records for which there is more than one matching right record. For a left record that matches N different right records (i.e., several right records with the same link field value), the joined view has N records, all of them having the same left fields' values but different right fields' values. As a result, the joined view may have more records than the left view. This option cannot be combined with A or A?
E, L or M	N/A	At most, one of these three options may be specified. These options do not affect the resulting joined view, but each one gives advisory information that may help in using indices to sort:   E: Specifies that each right record matches Exactly one left record L: Specifies that each right record matches at Least one left record (one or more left records) M: Specifies that each right record matches at Most one left record (zero or one left records)   If none of these three options is specified, the program assumes that there is no information on how many left records each right record may match.
Fields	Array	A list of aggregation specifications for right fields, overriding the default aggregation rules. Each aggregation specification is a two-element array containing a right field name (string), and an aggregations array. The aggregations array is a list of rules of the same format as arrays returned by GetFieldsAggregations(). It can contain one or more of the arrays {"Sum"}, {"Max"}, {"Min"}, {"Copy"}, {"Count"}, {"Avg"}, or {"Avg", weight_field}. This will override the default aggregation rules for the fields specified in this option
JoinLinkedString	JSON String	Sets the parameters for updating external data to a joined view.

 

Returns

A string indicating the name of the new joined view.

 

Notes

• By default, each left record appears exactly once in the joined view, linked to a right record with a matching link field value. If no right record has a matching link field value, the right field values appear as missing in the joined view for that record. If more than one right record has a matching link field value, one is chosen arbitrarily.

• The two-link fields should be either both numeric or both string-typed (in which case matching is case-sensitive). If the left link field is string-typed and the right link field is numeric, the left link field values are converted to numbers before matching. However, if the left link field is numeric and the right link field is string-typed, then all left records fail to match any right record.

• If the O option is specified, the resulting joined view is not mappable. (This is because there may be more than one record in the joined view that corresponds to a single geographic record.) If the O option is not specified, the resulting joined view is mappable only if its left view is mappable.

• In a joined view, the values reflect the current values in the component views. If a value in a component view changes, the corresponding value in any affected joined view is updated automatically.

• Field values in a joined view can be edited, except for those in the fields used for linking. All affected component views are updated automatically. The two link fields in a joined view cannot be edited, but it is possible to edit values on those same link fields in one of the component views. Such an operation can change the record linkage pattern in the joined view if the I or O option is specified; all changes are reflected automatically.

• It is not possible to add records to or delete records from a joined view. If a component view supports addition or deletion of records, however, the results are automatically reflected in any affected joined views.

• If the input joined_view_name is null, a default name is used.

• The A? option is useful because it allows the program to make an automatic decision about whether the right records should be aggregated. This decision is based on only a partial scan of the right link field values, however.

• For non-aggregate joined views, each field corresponds exactly to either a left field or a right field. Thus, the number of fields in the joined view equals that of the left view plus that of the right view.

• For aggregate joined views, there is exactly one field for each left field, one field for the right link field, and one Count() field (which indicates how many right records match that left record). In addition there can be four fields in the joined view for each non-link numeric right field; one for each of the Sum(), High(), Low() and Avg() aggregation methods. There can also be one field for each string-typed right field for the Copy() aggregation method, which copies the first matching record's value as a representative value. By default, the aggregation rules for each field are used (see GetFieldAggregations()). If the field has no rules, Count() and Sum() rules will be used. If, on the other hand, the right field is listed in the Fields option, a Count() field will only be created if the "Count" rule is one of the aggregation rules. Likewise, Sum(), High(), Low(), and Avg() fields will only be created if the "Sum", "Max", "Min", and "Avg" rules are listed, respectively.

• The default aggregation rules for numeric fields are Sum, Max, Min and Avg; for string fields is Copy; and for right fields (numeric or string) is Copy. This means that the field is the sum of values, the maximum, the minimum, the average of values, or a copy of the first value, respectively.

• The Copy aggregation rule can also be First or Dom.

• All option pairs except Fields have just null for the option value, as shown in the aggregate link example below.

• The JoinLinkedString can be used to generate a "geocoding" join to one of the layers in the Maptitude catalog (street, city, state, zip, zip3, county_name, county_fips, tract, cbsa)

Example 1:

// Link sales data by state to the State layer

view1 = JoinViews("Sales Information", "[U.S. States].Name", "Sales.State",)

// Get total and average Sales, nothing for ZIP

aggr = {{"SALES", {{"Sum"}, {"Avg"}}}, {"ZIP", null}}

// Link (aggregate) customer data by State

view2 = JoinViews("Customer Information", "[U.S. States].Name",

     "Customer.State", {{"A", }, {"Fields", aggr}})

 

 

 

Example 2:

 

// Join and link data in your an Excel file to the ZIP layer

// Step 1. open the Excel file

folder = RunMacro("G30 Tutorial Folder")

excel_file = folder + "Sample Customers United States.xlsx"

vw = OpenTable("My Customers", "EXCELX", {excel_file , "My Customers$"} , )

 

// Link the data into the excel file to the zip

linked_array = {"Type" : "ExcelX",

     "Spec" : {excel_file, "My Customers$"} ,  

     "LocationType": "ZIP",

     "JoinFieldNames": {"ZIP Code"},

     "ExtraFields" : {"Sales"}

    }

linked_string = "JSON: " + ArrayToJson(linked_array)

aggr = {{"QTR1 Sales", {{"Sum"}, {"Avg"}}}}

joined_view = JoinViews("MyJoin", "5-Digit ZIP Code.ZIP", vw+".ZIP Code",

                        {{"A", }, {"Fields",aggr},{"JoinLinkedString", linked_string}})

info_array = GetViewJoinInfo(joined_view, {{"Linked", True}})

 

//Note: Use RunMacro("Update Linked Records") to update the joined view when the underlaying Excel file changes

 

Error Codes

Error Type	Meaning
NotFound	Specified input views or fields do not exist

 

See Also

Function	Summary
AggregateTable()	Groups records in a view and computes summary statistics
CloseView()	Drops a view from the system
GetFieldAggregations()	Gets the aggregation rules for a field
JoinViewsMulti()	Creates a joined view by linking multiple fields in two existing views
OpenTable()	Opens a table from a file on disk and creates a view
SelfAggregate()	Groups the records in a view, based on a field, to create an aggregate view