JOIN command

Concept Information

Join tables

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.

  • PKEY primary_key_fields use the specified field or fields

    Fields are used in the order that you list them.

  • PKEY ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.
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.

  • FIELDS primary_fields include the specified field or fields

    Fields are included in the order that you list them.

  • FIELDS ALL include all fields from the table

    Fields are included in the order that they appear in the table layout.

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.

  • SKEY secondary_key_fields use the specified field or fields

    Fields are used in the order that you list them.

  • SKEY ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.
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.

  • WITH secondary_fields include the specified field or fields

    Fields are included in the order that you list them.

  • WITH ALL include all fields from the table

    Fields are included in the order that they appear in the table layout.

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)

The joined output table contains: Corresponding option in the Join dialog box
  • all matched primary records and the first matched secondary record

Matched primary and secondary 

(1st secondary match)

MANY

The joined output table contains: Corresponding option in the Join dialog box
  • all matched primary records and all matched secondary records
  • one record for each match between the primary and secondary tables

Matched primary and secondary 

(all secondary matches)

UNMATCHED

The joined output table contains: Corresponding option in the Join dialog box

  • unmatched primary records

Unmatched primary

PRIMARY

The joined output table contains: Corresponding option in the Join dialog box
  • all primary records (matched and unmatched) and the first matched secondary record

All primary and matched secondary

Note

The keyword BOTH is the same as specifying PRIMARY.

SECONDARY

The joined output table contains: Corresponding option in the Join dialog box
  • all secondary records (matched and unmatched) and all matched primary records

    Only the first instance of any duplicate secondary matches is joined to a primary record.

All secondary and matched primary

PRIMARY SECONDARY

The joined output table contains: Corresponding option in the Join dialog box
  • all primary and all secondary records, matched and unmatched

    Only the first instance of any duplicate secondary matches is joined to a primary record.

All primary and 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:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

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:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

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:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

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 sorting

The 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 sorting

The 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:

  • language ISO 639 standard language code
  • country ISO 3166 standard country code

    If you do not specify a country code, the default country for the language is used.

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.