Loop - Dataset
Declaration
<AMLOOP ACTIVITY="dataset" DATASET="text" FROM="number" TO="number" SORT="text (options)" SORTEDCOLUMN="number">
Description: Loops through the rows of the dataset specified. With each successive loop a series of steps are executed and the current row of the dataset is incremented. The loop ends when it reaches the end of the dataset or when a Break is encountered.
Practical Usage
Commonly used to loop a block of steps through the records (rows) in a dataset (which is a multi-column, multi-row variable). With each loop the current record (row) in the dataset is incremented until the last record is reached, at which point the loop ends.
General Parameters
|
Property |
Type |
Required |
Default |
Markup |
Description |
|---|---|---|---|---|---|
|
Dataset |
Text |
Yes |
(Empty) |
DATASET="datasetname" |
The name of the dataset in which to loop through. Information about the dataset this activity creates can be found below under Datasets. |
|
Start loop on row |
Text |
No |
(Empty) |
FROM="20" |
If enabled, specifies the record (row) that this activity should start on when looping through the dataset. All rows that exist before the specified row will be skipped. This parameter is disabled by default. |
|
End loop on or before row |
Text |
No |
(Empty) |
TO="82" |
If enabled, specifies the record (row) that this activity should stop on when moving through the dataset. If disabled, the end Row is set to the last row in the dataset. This parameter is disabled by default. |
Advanced Parameters
|
Property |
Type |
Required |
Default |
Markup |
Description |
|---|---|---|---|---|---|
|
Loop order |
Text (options) |
No |
Unsorted |
|
The sort order that should be applied to the dataset values before the loop begins. If using this activity with a SQL Query activity, in most cases it will be more efficient to perform the sorting as part of the query instead of using this option. If ascending or descending is specified, a valid column name to sort on must also be specified. The available options are:
|
|
Ordering column |
number |
No |
(Empty) |
SORTEDCOLUMN="LastName" |
The dataset column to sort on. This parameter is active only if the Loop order parameter is set to Descending alphabetical order . |
Description tab - A custom description can be provided on the Description tab to convey additional information or share special notes about a task step.
Error Causes tab - Specify how this step should behave upon the occurrence of an error. (Refer to Task Builder > Error Causes Tab for details.)
On Error tab - Specify what AWE should do if this step encounters an error as defined on the Error Causes tab. (Refer to Task Builder > On Error Tab for details.)
Datasets
A dataset is a collection of data, usually structured to include multiple columns and multiple rows. Each column represents a particular variable. Each row corresponds to a given member of the dataset in question. The data in a dataset is laid out like a table and is accessed in much the same way (e.g., by specifying the column and row where the data resides). When using datasets, the column name must be specified using the following format:
%datasetname.columnname%. |
Many actions create and populate a dataset, such as SQL Query, Stored Procedure, Get E-Mail, Loop Process, Get Process and Twitter activities, just to name a few. The fields contained within the dataset are determined by the action that was executed.
SQL Query Example
For example if the following query is executed by a SQL Query action.
SELECT firstname, lastname, company from customer where city='Los Angeles' |
Then the following dataset would be generated:
%datasetname.firstname% %datasetname.lastname% %datasetname.company% |
A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:
%mydatasetname.firstname%
Or you could combine two fields together like this:
%mydatasetname.firstname + " " + mydatasetname.lastname%
Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:
<AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">
Example
The sample AML code below can be copied and pasted directly into the Steps panel of the Task Builder.
Description: This sample task will use the appropriate 'Excel' actions in to grab data from the first few rows and columns of an Excel Document (rows 1 to 5 and columns A to C) and populate a dataset with the data. It will then loop through the rows of the dataset using the Loop Dataset action and show the data from each column in a message box. To ensure that this task works, select an Excel Document that includes some data from rows 1 to 5 and Column A to C when prompted.
<AMVARIABLE NAME="theExcelDoc"></AMVARIABLE> <AMVARIABLE NAME="theCurrentRow">0</AMVARIABLE> <AMSHOWDIALOG ACTIVITY="folder" WINDOWTITLE=""Open An Excel Document that includes some data from rows 1 to 5 and Column A to C. "" RESULTVARIABLE="theExcelDoc" /> <AMEXCELOPENWORKBOOK WORKBOOK="%theExcelDoc%" /> <AMEXCELCELLSTODATASET RESULTDATASET="theDataset" STARTCELLREF="A1" ENDCELLREF="C5" /> <AMLOOP TYPE="DATASET" DATASET="theDataset"> <AMSHOWDIALOG>The Data in Row %theCurrentRow%, Column A is: %theDataset.A% The Data in Row %theCurrentRow%, Column B is: %theDataset.B% The Data in Row %theCurrentRow%, Column C is: %theDataset.C% </AMSHOWDIALOG> <AMINCREMENTVARIABLE RESULTVARIABLE="theCurrentRow" /> </AMLOOP>