Extracting data
Extracting allows you to copy some or all of the records or fields from an Analytics table to a new Analytics table.
The new table can be:
- an identical copy containing all the source table records and fields
- a subset of the records in the source table
- a subset of the fields in the source table
- a subset of both the records and the fields in the source table
The existing sort order in the source table is maintained in the new table.
Note
Extracting data and appending it to the end of an existing Analytics table is a data combining technique. It is explained in the section on combining data. For more information, see Extracting and appending data.
The usefulness of extracting data
The following are some of the reasons for extracting data to a new table:
- produce a subset of only the data relevant to a particular analysis, and reduce file size and processing time
- use filters to isolate particular items in a separate table for further analysis
- preserve the integrity of an original data file by extracting its content to a working copy of the file
- convert computed fields to physical fields populated with the actual computed values
- extract data from a server table to a new, local table
- extract data from two or more related tables to a new Analytics table
The difference between extracting data and copying a table
The difference between extracting all data, and copying a table in the Navigator (), is that extracting creates a new source data file (.fil) as well as a new table layout, whereas copying creates only a new table layout that remains associated with the original source data file.
Extracting by record, by view, or by fields
When you extract data, you have the following options:
- Record extract entire records
- View extract all the fields in a view
- Fields extract a selection of individual fields
When you extract entire records, the record is copied exactly, including any data stored in undefined gaps in the table layout.
When you extract all the fields in a view, or individual fields, any undefined portion of a record is ignored, even if you extract all the fields in the source table.
Extracting computed fields
Computed fields remain as computed fields when you extract by record. They are converted to physical fields of the appropriate data type, and populated with the actual computed values, when you extract by view or by fields.
Extracting time data in a computed field
If a computed field contains local times with a UTC offset (for example, 23:59:59-05:00), the local times and the UTC offset are preserved when you extract by record.
When you extract by view or by fields, the local times and the UTC offset are converted to UTC without an offset. For example, 23:59:59-05:00 becomes 04:59:59.
Additional details about extracting by view
Selecting the View option in the Extract dialog box allows you to extract exactly the data that is currently displayed in the active view.
The following details apply when extracting by view:
| Which fields are extracted? |
Only fields that are currently displayed in the view are extracted. Any additional fields that are part of the table layout but not displayed in the view are not extracted. All fields in the view are extracted. If you want to extract a subset of fields, remove the unwanted fields from the view, create a new view with just the required fields, or use extract by fields instead of extract by view. |
|---|---|
| Field order | The fields are extracted in the order they appear in the view. If you want to extract the fields in a different order, rearrange them in the view, or create a new view with the fields in the desired order, prior to extracting. |
| Filtering | If a filter is currently applied to the view, only the data that meets the filter criteria is extracted. |
| Record notes | Records notes are extracted only if the RecordNote column has previously been added to the view. |
| Alternate column titles | If any alternate column titles are specified at the view level, extract by view preserves the view-level titles. If you use the syntax in the command log to rerun the extract command, alternate column titles specified in the table layout are used, and view-level titles are ignored. |
|
Scripts Command line |
Specifying extract by view is not supported in scripts or from the command line. When rendered in ACLScript, extract by view is actually an extract by fields ( EXTRACT FIELDS ) using all the fields in the active view, in the order in which they appear in the view. |
Extracting logical fields
Extracting logical fields requires that Include Filters in Field Lists is selected ().
Setting a control total
If you are extracting all the records in a table, or all the data in a view or a selection of fields, you can set a control total on a numeric field to verify that all the data is in fact extracted.
You set a control total for a field in the Table Layout dialog box. Once you have extracted the data, in the new table select to compare the input and output control totals. For more information, see Define a physical field.
Extracting data from server tables and local tables
You can extract data from both server tables and local tables. Data extracted from a server table can be saved to a table on the server, or on your local computer. Data extracted from a local table can be saved only to a table on your local computer.
Steps
You can extract some or all of the records or fields from an Analytics table and output them to a new Analytics table.
Note
Extracting data and appending it to the end of an existing Analytics table is a data combining technique. It is explained in the section on combining data. For more information, see Extract and append data.
Show me how
- Open the table from which you want to extract records or fields.
- Select .
- On the Main tab, select one of the
following:
- Record extract
entire records
The fields in the record are extracted in the order they appear in the table layout.
- View extract all the
fields in the current view
The fields are extracted in the order they appear in the view.
- Fields extract a selection
of individual fields
The fields are extracted in the order you select them.
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.
Note
If you are extracting one or more computed fields, selecting Record preserves the extracted fields as computed expressions.
Selecting View or Fields converts the extracted fields to physical fields of the appropriate data type and populates them with the actual computed values.
- Record extract
entire records
- If you selected Fields, do one
of the following:
Select the appropriate fields from the Extract Fields list.
Click Extract Fields to select the appropriate fields, or to create an expression, then click OK.
Click Extract Fields if you want to select fields from a child table in a table relation. The From Table drop-down list in the Selected Fields dialog box allows you to select the appropriate child table.
-
If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
Note
The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).
- Do one of the following:
In the To text box, specify the name of the new Analytics table.
Click To and specify the name of the new Analytics table, or select an existing table in the Save or Save File As dialog box to overwrite the table.
If Analytics prefills a table name, you can accept the prefilled name, or change it.
You can also specify an absolute or relative file path, or navigate to a different folder, to save the new table or overwrite an existing 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 save or overwrite the table, it is added to the open project if it is not already in the project.
-
If you are connected to a server table, do one of the following:
- Select Local to save the output table to the same location as the project, or to specify a path or navigate to a different local folder.
- Leave Local deselected to save the output table to the Prefix folder on a server.
Note
For output results produced from analysis or processing of Analytics Exchange server tables, select Local. You cannot deselect the Local setting to import results tables to Analytics Exchange.
- Select or deselect Use Output Table depending on whether you want the Analytics table containing the output results to open automatically upon completion of the operation.
- Click the More tab.
-
Select the appropriate option in the Scope panel:
- All – This option is selected by default. Leave it selected to specify that all records in the view are processed.
- First – Select this option and enter a number in the text box to start processing at the first record in the view 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 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 view based on a particular criterion or set of 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 in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.
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.
- If required, select EOF (End of file processing) to
force 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.
- Click OK.
- If the overwrite prompt appears, select the appropriate option.
Analytics 14.1 Help