Set reading from an excel sheet: parameter indexed over two sets - one of them previously loaded

Support
, ,
1

Hi people. I have an excel workbook with two different tables. In the first table, I have a costs parameter indexed over a set called product. In the another one, I have a parameter demand, indexed over two sets: the product set, and another set called T.

The first parameter is loaded successfully.

But, when I’m trying to load the other set (T-“Periods”) and parameter “Demand” I got several errors, I have read the book and the forum looking for a similar case, but I haven’t got an answer to my problem.

Some screenshots of the excel data:

image
image451×573 12.7 KB

image
image498×561 18.9 KB

Here’s my code:

reset;

set P;
set T;

param costos{P};
param demanda{P,T};

data;

table costos_prod IN "amplxl" "Book1.xlsx":
   P <- [Producto], costos~Costos;

table Demanda IN "amplxl" "Book1.xlsx":
   [T <- t~Periodo, p~Producto], demanda[i,t]~Dit;

load amplxl.dll;
read table costos_prod;
read table Demanda;

display costos;
display demanda; ```
2

In the Periodo column of your second spreadsheet, each member of T appears several times. Thus it is not possible to use that spreadsheet to read the Periodo values into the set T.

For your example, where the times are given by numbers 1, 2, 3, . . . , you could define T instead like this:

param nT integer > 0;
set T = 1..nT;

Then for data you only need to read one integer, the value of nT. You can use a table statement like this to read nT from a spreadsheet:

table Periodos IN "amplxl" "Book1.xlsx":
    [], nT~nPeriodos;

In the spreadsheet, the range Periodos would be just two cells: one containing nPeriodos, and one right under it containing the number of periods.

3

Thank you for your response dear Dr. Fourer. My doubt is, however, not yet resolved. What I have understood from the answer is that I have to create anoter table or range in excel just fot the Periodo set.

With that said, my doubt is how do I load the parameter Demanda (column Dit) to ampl. Would be the code below correct? Let us supose the param nT ... statement was added to the model.

. . .
table Periodos IN "amplxl" "Book1.xlsx":
    [], nT~nPeriodos;

table costos_prod IN "amplxl" "Book1.xlsx":
   P <- [Producto], costos~Costos;

table Demanda IN "amplxl" "Book1.xlsx":
   [t~Periodo, p~Producto], demanda[i,t]~Dit;

load amplxl.dll;
read table Periodos;
read table costos_prod;
read table Demanda;

display costos;
display demanda;
4

That looks good. Have you tried running it in AMPL?