The lookup functions in DIAL access data defined in external Lookup Files, which are comma- or tab-delimited tables of data.
lookup.open(lookup_name, filename, dimension_name, options)
Opens the specified Lookup file indicated by a local filename and assigns it to the lookup stored in the special Object Variable lookup_name. If dimension_name is specified, it must indicate a column in the Lookup File. If any walk statement walks through that Dimension, it automatically searches the Lookup File for a row that matches the current Dimension Value, and makes that row the current row. Supported options include:
| Option | Description |
|---|---|
| delim=tab | Sets the Lookup delimiter to be an ASCII tab. |
| delim=comma | Sets the Lookup delimiter to be a comma. |
| delim=<character> | Sets the Lookup delimiter to be a defined character. |
| dictfile=<filename> | Sets the specified filename as the dictionary describing the Lookup File. The dictionary must be a new-style dictionary describing a variable-format file. If this option is specified, the delim option is overridden by the dictionary file. |
lookup.set_row(lookup, row_num)
Sets the specified row for the Lookup file stored in the special Object Variable lookup, with the first row indicated by a row_num value of 1.
lookup.next(lookup)
Sets the row for the Lookup File stored in the special Object Variable lookup to be the next row in the Lookup File. If the current row is the last row, the Lookup row is undefined, and the function returns false. The function returns true if the Lookup row is successful. If the current row is undefined, the function does nothing.
lookup.defined(lookup_name)
Determines whether the lookup file row is defined. This function returns true (1) if the Lookup row indicates a valid row in the Lookup File. It returns false (0) if the Lookup row is not set, the Lookup row falls off the end of the Lookup File through a lookup.next() call, or if the lookup.find() function fails to find a matching row.
lookup.find(lookup, column_name1, column_value1, ...)
Searches the specified lookup for the first row that matches the specified criteria, where the value of column_name1 is the string column_value1, and the value of column_name2 is the string column_value2, and so on. The function returns true if the search succeeds and false otherwise. If the search fails, the row number for the Lookup File is undefined.
lookup.parse_error(lookup_name)
Determines whether there were parse errors when reading in the specified lookup file. Returns true (1) if any parse errors occurred.
lookup.sort()
Sorts the in-memory copy of the lookup file based on the string values of the column_name. There are two argument formats for lookup.sort():
lookup.sort(lookup, column_name)
or
lookup.sort(lookup, column_name1, options1, column_name2, options2, ...)
Accessing the lookup file sequentially results in the values in the column being sorted. When using lookup.sort with the optional sort arguments, the lookup is sorted according to multiple columns, in the order listed. Supported options include:
| Option | Description |
|---|---|
| ascending | The Lookup is sorted so that the corresponding Column values appear in ascending order. This is the default sort order. |
| descending | The Lookup is sorted so that the corresponding Column values appear in descending order. |
| numeric | The corresponding Column is sorted in numeric order. By default, Columns are sorted as strings. |
Lookup Example
The following script connects to the Diveline Server, opens the an_sales.dvp DivePlan and the an_salesmanagerlu.txt lookup file. It then dives into and walks the Sales Manager dimension in the sales model and calculates the percent variance from the Q1 Goal. This information is then written to the console.
diveline.connect("docs.dimins.com:2130","dial_user","dial_user");
diveline.set_project("myProject");
model.open(model,"/models/an_sales.dvp");
lookup.open(manager_lu, "/di/projects/myProject/data/an_salesmanagerlu.txt", "Sales Manager", "delim=tab");
dive (model, "Sales Manager") {
walk (model, "Sales Manager") {
if (lookup.defined(manager_lu) AND manager_lu."Q1 Goal" != "") {
Q1_Pct_var = pct_var(calc[Revenue Total Q1],manager_lu."Q1 Goal");
console.writeln (Dimension[Sales Manager]);
console.writeln (" Goal: "+format_number(manager_lu."Q1 Goal","$##,##0.##"));
console.writeln (" Variance:"+format_number(Q1_Pct_var,"##,##0.00")+"%");
}
else {
console.writeln ("Goal not defined for Sales Manager: \t" +Dimension[Sales Manager]);
}
console.writeln(" ");
}
}
diveline.disconnect();
