Extract and append data

Concept Information

You can extract records or fields from one Analytics table and append them as a group to the end of another Analytics table. The records or fields must have an identical structure in the two tables. The two tables can be sorted or unsorted. The resulting combined table is considered unsorted.

Steps

In the Navigator, open the table from which you want to extract records or fields.
Select Data > Extract.
On the Main tab, select one of the following:
- Record extracts entire records.
- View extracts all the fields in the current view.
  Note
  The number, selection, and order of the fields in the view must exactly match the number, selection, and order of fields in the target table’s table layout.
- Fields extracts a selection of individual fields.
If you selected Fields, select the appropriate fields from the Extract Fields list.
Tip
You can Ctrl+click to select multiple non-adjacent fields, and Shift+click to select multiple adjacent fields.
Note
The number, selection, and order of the fields you select must exactly match the number, selection, and order of fields in the target table’s table layout.
In the To text box, specify the name of the target table.
On the More tab:
1. (Optional) To specify that only a subset of records are processed, select one of the options in the Scope panel.
2. Click OK.

Extract dialog box options

The tables below provide detailed information about the options in the Extract dialog box.

Main tab

Options – Extract dialog box	Description
Record View Fields	Specifies the extraction method. Record extracts entire records. The fields in the record are extracted in the order they appear in the table layout. View extracts all the fields in the current view. The fields are extracted in the order they appear in the view. Fields extracts a selection of individual fields. The fields are extracted in the order you select them. If you are extracting one or more computed fields: select Record to preserve the extracted fields as computed expressions select View or Fields to convert the extracted fields to physical fields of the appropriate data type and populate them with the actual computed values Note You cannot append computed and physical fields to each other. For more information, see Extracting and appending computed fields. If you want to extract data from a child table in a table relation: select Fields, or select View if the child table fields have previously been added to the view. You cannot extract child table data using the Record option.
Extract Fields	If you selected Fields, specifies the fields to extract. You can select the appropriate fields from the Extract Fields list. You can also click Extract Fields to select the appropriate fields, or to create an expression, then click OK. If you want to select fields from a child table in a table relation: Click Extract Fields. The From Table drop-down list in the Selected Fields dialog box allows you to select the appropriate child table.
If	(Optional) Allows you to create a condition to exclude records from processing. You can enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
To	Specifies the name and location of the target table. You can specify the name of the target table in the To text box. You can click To and specify the name of the target table, or select an existing table in the Save or Save File As dialog box as the target table. You can also specify an absolute or relative file path, or navigate to a different folder, to append data to a target table in a location other than the project location. For example: C:\Results\GL_2011.fil or Results\GL_2011.fil. Regardless of where you append data, the target table is added to the open project if it is not already in the project.
Local	If you are connected to a server table, specifies where to save the output table. Local selected saves the output table to the same location as the Analytics project, or to a specified path, or location you navigate to. Local deselected saves the output table to the Prefix folder on AX Server.
Use Output Table	Specifies whether the Analytics table containing the output results opens automatically upon completion of the operation.

More tab

Options – Extract dialog box	Description
Scope panel	Specifies which records in the source table are processed: All (default) all records in the source table are processed. First select this option and enter a number in the text box to start processing at the first record in the source table and include only the specified number of records. Next select this option and enter a number in the text box to start processing at the currently selected record in the source table view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row. While select this option to use a WHILE statement to limit the processing of records in the source table based on criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder. A WHILE statement allows records to be processed only while the specified condition evaluates to true. You can use the While option in conjunction with the All, First, or Next options. Note The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering. If a view is quick sorted, Next behaves like First.
EOF (End of file processing)	(Optional) Forces the extract operation to execute one more time when the end of a table is reached. The EOF parameter is usually used if you are extracting records as part of a larger analytic process and the Extract command occurs inside a group in a script. If you are extracting records based on a comparison between sequential records, you may need to use EOF to ensure the final record in a table is extracted.
Append To Existing File	Specifies that the output results are appended (added) to the end of an existing Analytics table. You can select Append To Existing File if you are certain the records or fields and the target table are identical in structure. You can leave Append To Existing File deselected if you want Analytics to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly. Note Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure. For more information about appending and data structure, see Appending output results to an existing table.
OK	Executes the operation. If the overwrite prompt appears, select Append. If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending output results to an existing table.