Attempting to read in .xls with two key columns and display in AMPL

[AG]

Dear AMPL Community,

I am working on the AMPL IDE, and I am attempting to replicate code examples from Section 10.3 of the Second edition of AMPL A Modeling Language for Mathematical Programming to my particular program. In particular, I am attempting to read into AMPL a table from a .xls which has two key variables (TIME, ZONE) which correspond to two sets I have defined in a .mod.

When attempting to display any variable from the table times on the console, I am encountering an error "no data for set TIME".

I am requesting assistance since I was able to replicate these steps to display variables for the same .mod and a similar .xls which contained only 1 key column to match 1 set. See the screenshot below of my AMPL Console which displays the errors.

Thank you very much for any assistance you can suggest.

AG

[AMPL Google Group]

When you have a table statement like this,

table times ...:
[TIME,ZONE], time_step, zoneTemperatureReported, ...

then "read table times;" reads values for the parameters time_step, zoneTemperatureReported, etc., but it does not read values for the sets TIME and ZONE. You will need separate table and read table statements to get the values for TIME and for ZONE; and you should read those tables before table times.

As you may have seen, you can use a single table statement for reading the members of TIME and one or more parameters that are indexed only over TIME; and similarly for ZONE. You just need to do that before reading the data that is indexed over TIME and ZONE.

--
Robert Fourer
am...@googlegroups.com

{#HS:2042541812-112437#}

[AG]

Robert,

Thank you for your clarification. I would like to clarify my understanding. Indeed, I have a statement which reads the members of ZONE as well as the parameters indexed over ZONE. This was successfully called into AMPL. The table statement in my original post is meant to read members who are indexed over ZONE and TIME. In other words, I don't anticipate creating a table nor reading in members which are indexed over TIME because these members must also be associated with ZONE.

If I am understanding correctly your suggestion to my question, you are suggesting that I need to read in a table that is indexed over TIME, and then read in the table indexed over ZONE and TIME such that I have three read table statements? But if this is the case, I'm not sure reading the table indexed over only TIME would suffice since the way my data is structured each row in this table must also be associated with the ZONE set.

AG

[AMPL Google Group]

Even if you do not have any parameters indexed over TIME, you will need to create a table to read the members of TIME from the spreadsheet. So indeed you will have 3 tables and 3 read table statements. As an example, to define the additional table for the set TIME, you could write

table timeset IN "ODBC" "BAS_Data_Time_Dependent.xls":
   TIME <- [TIME];

Then in the range timeset in the spreadsheet, you will have just a one-column table of the members of set TIME, with "TIME" as its heading, and the members in the cell below it.

(Note that if you switch to using .xlsx spreadsheet files at some point, then you can use our new direct spreadsheet interface, which supports 2D tables and does not rely on ODBC.)

--
Robert Fourer
am...@googlegroups.com

{#HS:2042541812-112437#}

On Wed, Oct 19, 2022 at 5:24 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Robert,

Thank you for your clarification. I would like to clarify my understanding. Indeed, I have a statement which reads the members of ZONE as well as the parameters indexed over ZONE. This was successfully called into AMPL. The table statement in my original post is meant to read members who are indexed over ZONE and TIME. In other words, I don't anticipate creating a table nor reading in members which are indexed over TIME because these members must also be associated with ZONE.

If I am understanding correctly your suggestion to my question, you are suggesting that I need to read in a table that is indexed over TIME, and then read in the table indexed over ZONE and TIME such that I have three read table statements? But if this is the case, I'm not sure reading the table indexed over only TIME would suffice since the way my data is structured each row in this table must also be associated with the ZONE set.

AG

On Wed, Oct 19, 2022 at 3:06 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

When you have a table statement like this,

table times ...:
[TIME,ZONE], time_step, zoneTemperatureReported, ...

then "read table times;" reads values for the parameters time_step, zoneTemperatureReported, etc., but it does not read values for the sets TIME and ZONE. You will need separate table and read table statements to get the values for TIME and for ZONE; and you should read those tables before table times.

As you may have seen, you can use a single table statement for reading the members of TIME and one or more parameters that are indexed only over TIME; and similarly for ZONE. You just need to do that before reading the data that is indexed over TIME and ZONE.

--
Robert Fourer
am...@googlegroups.com