### Reading multi-dimensional Excel data with ExcelRetrieveTable

A lot of applications need to read the input data for the model from a Spreadsheet file. AIMMS supports both reading data from and writing data to spreadsheets. For retrieving data from Excel into a parameter in your AIMMS model, you can make use of the functions **ExcelRetrieveParameter** and **ExcelRetrieveTable** (see the section Excel functions in the Function Reference).

There are some differences between these two functions. The first one is that ExcelRetrieveParameter can be used to read in the data for scalar, 1, and 2 dimensional data from Excel. With the ExcelRetrieveTable, you can also read in higher dimensional data. The second difference is that the ExcelRetrieveParameter function assumes the data in Excel is in the same order as the elements in your sets in AIMMS. This means that if your set in AIMMS has the elements in the order {a, c, b}, the function will assume that the first item it reads from Excel corresponds to element a, the second item corresponds to element c, and the third item corresponds to element b. This behavior can lead to inconsistencies if you are not aware about this.

The ExcelRetrieveTable function tackles this last problem by not only reading the data, but also reading the information about the elements the data corresponds to. To be able to do this, the ExcelRetrieveTable function requires some additional arguments about ranges. In the rest of this article, we will explain the different arguments for this function.

When using the ExcelRetrieveTable function, you must provide the following arguments:

ExcelRetrieveTable( ExcelWorkbook : , Parameter : , DataRange : , RowsRange : , ColumnsRange : , Sheet : , AutomaticallyExtendSets : ) |

The ExcelWorkbook and Sheet arguments tell AIMMS which sheet and workbook to use. The parameter argument tells AIMMS to which identifier the data from Excel should be read.

The three ranges (Data, Rows, and Columns) that are required can be visualized as follows:

Note that in the example above, the three ranges are consecutive in Excel (i.e. they are next to each other). This does not need to be the case, i.e. there might be empty columns between the row range and the data range, and empty rows between the column range and the data range. You only need to ensure that the following conditions hold:

- dimension of parameter in AIMMS;
- Number of rows in row range = Number of rows in data range;
- Number of columns in column range = Number of columns in data range.

During the execution of the call, AIMMS will actually verify that the above conditions indeed hold and return with an error if this is not the case.

If all the conditions hold, then ExcelRetrieveTable must match the columns of the row range and the rows of the column range to the indices for the parameter denoted by the parameter argument. The way this matching done is fixed and means that the order of the indices in your AIMMS model must match the order of the indices in the following way: AIMMS will assume the first index of the AIMMS parameter corresponds to the first column in the row area, the second index to the second column, etc. After the columns of the row range, the next index corresponds with the first row of the column range. The total mapping follows the following order:

This mapping is displayed in the example below:

In this example, the parameter in AIMMS will get the value 3.14 for the indices with the values (aa,bb,dd,ee,ww,xx,yy,zz). The three ranges in the above Excel example would be the following:

- Row range: A8:D18
- Column range: E4:K7
- Data range: E8:K18

The final argument (AutomaticallyExtendSets) is a binary argument that instructs AIMMS to extend the sets corresponding to the indices with values from the row and column range if it has value 1. If the value is 0 and elements exist in the row or column range that do not exist in the corresponding AIMMS Set, the ExcelRetrieveTable will result in an error.

To write multi-dimensional data from AIMMS to Excel, you can use the function **ExcelAssignTable**. This function uses the same three ranges in the same way as the ExcelRetrieveTable function does.

**Additional information**: please note that with AIMMS 3.12 FR1 and up, there is also support for OpenOffice Calc. Because this means that the functionality is generic instead of specific to Excel only, the naming has changed to Spreadsheet::RetrieveTable (i.e. the Excel prefix is replaced by a Spreadsheet:: prefix for all Excel functions). Please see the Function Reference within your AIMMS installation for more details about these spreadsheet functions.

### Using GMP functions instead of regular solve statement

In the simplest form solving a Mathematical Program identifier is done by using the intrinsic **solve **statement of AIMMS:

solve MathProgram ; |

For the majority of the AIMMS modelers, this suffices for their needs. Whenever you want to have more advanced control over what happens, you have to start working with Generated Mathematical Programs (GMP). With GMP’s, you have full control over the constraint matrix: you can edit coefficients and add new constraints and variables.

The initial transition from using only standard Mathematical Program identifiers to using GMP functions is very easy and boils down to introducing an additional element parameter and changing the original solve statement into two statements: one for generating the GMP and one for solving the GMP. The element parameter you need is the following:

ELEMENT PARAMETER: identifier : genMathProgram range : AllGeneratedMathematicalPrograms |

The original one-line solve statement now needs to be converted to the following two lines:

genMathProgram := gmp::Instance::Generate(MathProgram) ; gmp::Instance::Solve(genMathProgram ) ; |

The first line generates the GMP and the second line solves this GMP. With these two minor additions, you have moved to using GMP’s. For more information about what you can do with the GMP’s, you can take a look at the Chapter “Implementing Advanced Algorithms for

Mathematical Programs” in the Language Reference and the GMP section in the Function Reference.

**Additional information**: The exact workings of gmp::Instance::Solve statement can actually be emulated in a couple of lower-level GMP statements. If you look at the Language Reference, you will see that you can emulate its behavior with the following calls:

! Create a solver session for genMP, which will create an element ! in the set AllSolverSessions, and assign the newly created element ! to the element parameter session. session := GMP::Instance::CreateSolverSession(genMP); ! Copy the initial solution from the variables in AIMMS to ! solution number 1 of the generated mathematical program. GMP::Solution::RetrieveFromModel(genMP,1); ! Send the solution stored in solution 1 to the solver session GMP::Solution::SendToSolverSession(session, 1); ! Call the solver session to actually solve the problem. GMP::SolverSession::Execute(session); ! Copy the solution from the solver session into solution 1. GMP::Solution::RetrieveFromSolverSession(session, 1); ! Store this solution in the AIMMS variables and constraints. GMP::Solution::SendToModel(genMP, 1); |

### Generate indexed constraint, except for certain elements

When you have an indexed constraint, sometimes you want this constraint not to be generated for certain elements of the index domain.

Examples of these cases are:

- In the constraint you are referring to the previous or the next element. This means that the constraint should not be generated for the first element and the last element, respectively. A simple example of this is a Stock Balance constraint:

StockLevel(t) = StockLevel(t-1) + AmountProduced(t) – AmountSold(t)

- You want to create a constraint that is indexed over two indices of the same set for all combinations, except for the situation where the two indices refer to the same element. An example would be a precedence constraint: a task i must be either before or after another task j and this constraint should be created for all i,j such that i≠j

You can achieve this goal by restricting the index domain of the constraint with a domain condition. A domain condition can be introduced with the | operator (the so-called such-that operator). With this operator, AIMMS will only consider those elements (or combination of elements in case of multiple indices) for which the expression after the | operator holds a non-zero value.

For the first example, in case of using the previous element in the constraint, you would like the constraint to be generated for all timeperiods t, except for the first one. This can be expressed in AIMMS by setting the attributes of the constraint as follows:

CONSTRAINT: identifier : StockBalanceConstraint index domain : t | ord(t) > 1 definition : StockLevel(t) = StockLevel(t-1) + AmountProduced(t) - AmountSold(t) |

The intrinsic ord function returns the position of the element in the set. The domain condition (the part after the | operator) will only have a non-zero value for those elements that are not on the first position of the set. This means that this constraint will be generated for all t, except for the first one.

For the second example, if we have a binary variable TaskStartsBeforeOther(i,j) that gets the value 1 in case task i starts before task j and 0 otherwise, we can model the constraint that for each combination of tasks it must hold that one of the two is started before the other one. This constraint is not valid for the combination where task i is equal to task j. This can be achieved by setting the attributes of the constraint as follows

CONSTRAINT: identifier : EitherBeforeOrAfter index domain : (i,j) | i <> j definition : TaskStartsBeforeOther(i,j) + TaskStartsBeforeOther(j,i) = 1 |

### Getting value of a dynamic identifier

With the addition of Model Edit Functions (MEF) to AIMMS 3.11, a lot of things that were previously impossible to do with AIMMS became possible.

One simple example of something that previously was not possible is to ‘dereference’ an element parameter with range AllIdentifiers, to get the value of the identifier that was denoted by the element parameter if this identifier was not scalar. In case the identifier that is referred to is scalar, you could use the ScalarValue intrinsic function (see Function Reference).

For example, if you had an element parameter epMySelectedSourceIdentifier with range AllIdentifiers that points to indexed identifier ‘pSourceParameter1′, it was not possible to get the actual value of the pSourceParameter1 for a given element via this element parameter.

With MEF, it now is possible to create a procedure at runtime that retrieves the value of the identifier denoted by the element parameter. To keep this example easy, we demonstrate how you can do this for a scalar identifier, although an intrinsic function for this already exists. You can easily extend the example below to work on indexed identifiers also.

For the example, we consider the following parameters and element parameters:

PARAMETER: identifier : pSourceParameter1 ; PARAMETER: identifier : pSourceParameter2 ; ELEMENT PARAMETER: identifier : epSelectedSourceIdentifier range : AllIdentifiers ; PARAMETER: identifier : pTargetValue ; |

For actually creating and referring to the runtime identifiers, we need some additional element and string parameters:

ELEMENT PARAMETER: identifier : epRuntimeLibrary range : AllIdentifiers ; ELEMENT PARAMETER: identifier : epRuntimeProcedure range : AllProcedures default : 'prDummyProcedure' ; STRING PARAMETER: identifier : psRuntimeProcedureBody ; |

The epRuntimeProcedure element parameter must have the default attribute set, as we must run the procedure later on via the apply statement, which requires the default attribute to be set.

Now we can create a procedure that will get two arguments, the source identifier and the target identifier. This procedure will then first create a runtime procedure that does the assignment of the value of the source identifiers to the target identifier, and then execute this procedure with the apply statement. The code of the procedure is the following:

!If there already exists an identifier with the name !RuntimeLibrary, we must delete it first if 'RuntimeLibrary' in AllIdentifiers then me::Delete('RuntimeLibrary') ; endif ; !Now we create the runtime library epRuntimeLibrary := me::CreateLibrary( libraryName : "RuntimeLibrary" , prefixName : "rtl") ; !Now we create the runtime procedure epRuntimeProcedure := me::Create( name : "prRuntimeProcedure" , newType : 'Procedure' , parentId : epRuntimeLibrary , pos : 0 ) ; !Now that we have the runtime procedure identifier, we can !create the body for this procedure ! !What we would like to have as the code for this procedure is : ! epTarget := epSource !In the current procedure we know the name of the identifier !denoted by epSource and epTarget, so we can create the body as !follows: psRuntimeProcedureBody := epTarget + " := " + epSource + " ; \n" ; !Now set the body of the runtime procedure me::SetAttribute( runtimeId : epRuntimeProcedure , attr : 'body' , txt : psRuntimeProcedureBody ) ; !Now compile the runtime library me::Compile( epRuntimeLibrary ) ; !And run the procedure via the apply statement apply(epRuntimeProcedure) ; |

Please note that this procedure has two arguments, epSource and epTarget, both of which are element parameters with range AllIdentifiers. Also, the earlier mentioned additional element and string parameters for MEF could be local identifiers for this procedure.

The above procedure can now be called with the following example code:

pSourceParameter1 := 5 ; epSelectedSourceIdentifier := 'pSourceParameter1' ; prGetValueOfIdentifer(epSelectedSourceIdentifier,'pTargetValue'); dialogmessage("Value = " + pTargetValue) ; |

Keep in mind that the above code does not do any error checking. This means that you could try to assign the value of a string parameter to a numerical parameter, which would result in a runtime error. Other possibilities are that the body of the runtime procedure contains a syntax error, in which case the me::compile statement will result in an error. Please see the AIMMS Language Reference (section “Raising and handling warnings and errors”) for more information on handling such errors with the AIMMS error handling.

A complete project containing the above source can be downloaded as an AimmsPack:

### Welcome to the AIMMS Blog

Welcome to the AIMMS blog. Via this blog, we would like to update you with new features in AIMMS, as well as offer you tips and tricks on how to get the most out of AIMMS.

Although comments are enabled, please note that the main purpose of this blog is not to be a support forum. If you have questions about specific posts, you are welcome to post them as comments under the posts. In case you want to include AIMMS code in your comment, please use the following HTML tag around it:

<pre lang="aim"> Place your AIMMS code here. </pre>

Using this pre-tag will ensure your code is syntax-highlighted, which should keep it readable.

If you have generic support questions about AIMMS, we encourage you to post them on our AIMMS Google group. On this Google group, there are a number of experienced AIMMS users present who try to answer the questions that are posted.