Audit Command Language – Extract Statement




Hello again my fellow data analysts. I hope you are having fun. A good sign would be to check if you feel you are making some progress in your professional projects at the work. A sense of progress is a kind of learning. Hoping that is the case, lets continue with ‘Audit Command Language Tutorial for Beginners’.

We will be continuing our discussion on ACL statements. In this post, we will look at the extract statement. The name can be misleading as it may lead some folks to confuse it with the ‘Export’ statement. Unlike the Export statement, which exports the contents of a data set into a new an external application, the extract statement simply extract the contents (as it is or a subset) of one data set into a new or an already existing data set. The sample script below would give a sense of the syntax and application of the statement.

Syntax:

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST

What is this script is doing is it simply opens  the table ‘Payroll’ and extracts its contents into a new table ‘Test’. These two tables would be exactly alike. Let us consider another example where we only extract a subset of the original table.

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST_A if alltrim(WorkDept) = “B01”

In the above script, the new table, would only contain lines from the original ‘Payroll’ table where the field ‘WorkDept’ has the value ‘B01’.

In  another scenario, we may also extract the contents of an existing table into another already existing table. This can be done by using the ‘Append’ keyword at the end of the statement. This would only be possible if the both the tables have the same layout i.e. same number of fields with the same lengths for all fields. Such a statement would be written as:

OPEN PAYROLL
EXTRACT FIELDS ALL TO TEST_A if alltrim(WorkDept) = “C01″ APPEND

In the above example we have combined the tables created the in previous example ‘Test_A” with the entries from the original table ‘Payroll’ where the field ‘WorkDept’ has values ‘B01’ & ‘C01’.

The same can be done using the GUI as well. See the screenshots below to achieve the results as per the example above:

For your reference, the above procedure is also detailed in a video. Please see the video demo below:

Try out these examples and keep experimenting. The more you fiddle around the more your learn.

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.