JOIN command
Concept Information
Combines fields from two Analytics tables into a new, single Analytics table.
Note
To use fuzzy matching to join tables, see FUZZYJOIN command.
Syntax
JOIN {PKEY primary_key_fields|PKEY ALL <EXCLUDE field_name <...n>>} {FIELDS primary_fields|FIELDS ALL <EXCLUDE field_name <...n>>} {SKEY secondary_key_fields|SKEY ALL <EXCLUDE field_name <...n>>} <WITH secondary_fields|WITH ALL <EXCLUDE field_name <...n>>> {no_keyword|MANY|UNMATCHED|PRIMARY|SECONDARY|PRIMARY SECONDARY} <IF test> TO table_name <LOCAL> <OPEN> <WHILE test> <FIRST range|NEXT range> <APPEND> <PRESORT> <SECSORT> <ISOLOCALE locale_code>
Parameters
Name | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
PKEY primary_key_fields | PKEY ALL |
The key field or fields, or expression, in the primary table.
|
||||||||||||||||||||||||
EXCLUDE field_name optional |
Only valid when performing a join using PKEY ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune PKEY ALL, by excluding the specified fields. EXCLUDE must immediately follow PKEY ALL. For example: PKEY ALL EXCLUDE field_1 field_2 |
||||||||||||||||||||||||
FIELDS primary_fields | FIELDS ALL |
The fields or expressions from the primary table to include in the joined output table.
Note You must explicitly specify the primary key field or fields if you want to include them in the joined table. Specifying FIELDS ALL also includes them. |
||||||||||||||||||||||||
EXCLUDE primary_fields optional |
Only valid when performing a join using FIELDS ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune FIELDS ALL, by excluding the specified fields. EXCLUDE must immediately follow FIELDS ALL. For example: FIELDS ALL EXCLUDE field_1 field_2 |
||||||||||||||||||||||||
SKEY secondary_key_fields | SKEY ALL |
The key field or fields, or expression, in the secondary table.
|
||||||||||||||||||||||||
EXCLUDE field_name optional |
Only valid when performing a join using SKEY ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SKEY ALL, by excluding the specified fields. EXCLUDE must immediately follow SKEY ALL. For example: SKEY ALL EXCLUDE field_1 field_2 |
||||||||||||||||||||||||
WITH secondary_fields | WITH ALL optional |
The fields or expressions from the secondary table to include in the joined output table.
Note You must explicitly specify the secondary key field or fields if you want to include them in the joined table. Specifying WITH ALL also includes them. You cannot specify WITH if you are using the UNMATCHED join type. |
||||||||||||||||||||||||
EXCLUDE field_name optional |
Only valid when performing a join using WITH ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune WITH ALL, by excluding the specified fields. EXCLUDE must immediately follow WITH ALL. For example: WITH ALL EXCLUDE field_1 field_2 |
||||||||||||||||||||||||
no_keyword | MANY | UNMATCHED | PRIMARY | SECONDARY | PRIMARY SECONDARY |
The type of join to perform. no_keyword (omit all join-type keywords)
MANY
UNMATCHED
PRIMARY
Note The keyword BOTH is the same as specifying PRIMARY. SECONDARY
PRIMARY SECONDARY
|
||||||||||||||||||||||||
IF test optional |
A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT). Note For most join types, an IF condition applies only to the primary table. The one exception is a many-to-many join, in which the IF condition can also reference the secondary table. To reference the secondary table you must specify a fully qualified field name (table_name.field_name). For example: IF Customer.State="NY" |
||||||||||||||||||||||||
TO table_name |
The location to send the results of the command to:
|
||||||||||||||||||||||||
LOCAL optional |
Saves the output file in the same location as the Analytics project. Note Applicable only when running the command against a server table with an output file that is an Analytics table. The LOCAL parameter must immediately follow the TO parameter. |
||||||||||||||||||||||||
OPEN optional |
Opens the table created by the command after the command executes. Only valid if the command creates an output table. |
||||||||||||||||||||||||
WHILE test optional |
A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached. |
||||||||||||||||||||||||
FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
||||||||||||||||||||||||
APPEND optional |
Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical:
Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result. |
||||||||||||||||||||||||
PRESORT optional |
Sorts the primary table on the primary key field before executing the command. Note You cannot use PRESORT inside the GROUP command. Indexing instead of sortingThe primary table can be indexed instead of sorted. With large tables, indexing instead of sorting may reduce the time required to join the tables. If you are joining two tables using an indexed common key field, omit PRESORT and SECSORT. |
||||||||||||||||||||||||
SECSORT optional |
Sorts the secondary table on the secondary key field before executing the command. Note You cannot use SECSORT inside the GROUP command. Indexing instead of sortingThe secondary table can be indexed instead of sorted. With large tables, indexing instead of sorting may reduce the time required to join the tables. If you are joining two tables using an indexed common key field, omit PRESORT and SECSORT. |
||||||||||||||||||||||||
ISOLOCALE locale_code optional |
Note Applicable in the Unicode edition of Analytics only. The system locale in the format language_country. For example, to use Canadian French, enter fr_ca. Use the following codes:
If you do not use ISOLOCALE, the default system locale is used. |
Examples
Join two tables as a way of discovering employees who may also be vendors
The examples below join the Empmast and Vendor tables using address as the common key field (the Address and Vendor_Street fields).
The JOIN command creates a new table with matched primary and secondary records, which results in a list of any employees and vendors with the same address.
OPEN Empmast PRIMARY
OPEN Vendor SECONDARY
JOIN PKEY Address FIELDS Empno First Last Address SKEY Vendor_Street WITH Vendor_No Vendor_Name Vendor_Street TO "Employee_Vendor_Match" OPEN PRESORT SECSORT
This version of the JOIN command includes all fields from the primary and secondary tables in the joined output table.
OPEN Empmast PRIMARY
OPEN Vendor SECONDARY
JOIN PKEY Address FIELDS ALL SKEY Vendor_Street WITH ALL TO "Employee_Vendor_Match" OPEN PRESORT SECSORT
This version of the JOIN command uses an IF condition to restrict the joined output table to employees and vendors with addresses in California.
Note that the join type is MANY, which is required if you want an IF condition to reference a secondary table. The name of the field in the secondary table must be fully qualified ( Vendor.Vendor_State ).
OPEN Empmast PRIMARY
OPEN Vendor SECONDARY
JOIN PKEY Address FIELDS ALL SKEY Vendor_Street WITH ALL IF State="CA" AND Vendor.Vendor_State="CA" TO "Employee_Vendor_Match" OPEN PRESORT MANY SECSORT
Join two tables as a way of discovering accounts receivable records with no matching customer
The example below joins the Ar and Customer tables using Customer Number (CustNo) as the common key field.
The JOIN command uses the UNMATCHED join type to create a new table with unmatched primary records, which results in a list of Ar records that are not associated with any Customer record.
OPEN Ar PRIMARY
OPEN Customer SECONDARY
JOIN PKEY CustNo FIELDS CustNo Due Amount SKEY CustNo UNMATCHED TO "CustomerNotFound.fil" OPEN PRESORT SECSORT
Remarks
For more information about how this command works, see Joining tables.