Write spreadsheet file with indexed set

[Vivi]

Hello,

I would like to write the parameters ( Cd, Fs, Ef ) and also the sets (set F1T, set F1H) that I have given the sample data below in a spreadsheet file to make then read by Ampl. I am not sure I'am writing then in the correct way.

I am writing the aks for your help.

# Sets
set ND
set F1 {ND} ;
set F1T {d in ND} within F1[d] ;
set F1H {d in ND} within F1[d] ;
set F1E {d in ND} within F1[d] ;

# Parameters
param Cd {d in ND, t in (F1T[d] union F1H[d])} >=0 ;
param Fs {d in ND, t in (F1T[d] union F1H[d])} >=0 ;
param Ef {d in ND, t in (F1T[d] union F1H[d])} >=0 ;

# Data
set ND := DC1
set F1[DC1]:= TT1 TT2 TH1 TH2 TE1 TE2 ;
set F1T [DC1]:= TT1 TT2 ;
set F1H [DC1]:= TH1 TH2 ;
set F1E [DC1]:= TE1 TE2 ;

param Cd:   TT1   TT2   TH1   TH2 :=
          DC1    10      20     10      30        ;

param Fs:   TT1   TT2   TH1   TH2 :=
          DC1   40      10      20     30       ;

param Ef:   TT1   TT2   TH1   TH2 :=
          DC1   20      30     10      40             ;

Best regards.

[AMPL Google Group]

You can read the sets F1T and F1H using indexed collections of tables:

table setF1T {d in ND} IN "amplxl" "setofsets2.xlsx":
   F1T[d] <- [(d)];
read table setF1T;

table setF1H {d in ND} IN "amplxl" "setofsets2.xlsx":
   F1H[d] <- [(d)];
read table setF1H;

Reading the parameters is then straightforward:

table CdFsEf IN "amplxl" "setofsets2.xlsx":
   [ND,F1], Cd, Fs, Ef;
read table CdFsEf;

You can test this out using the attached script and spreadsheet.

--
Robert Fourer
AMPL Forum

{#HS:2154005722-113993#}

[Vivi]

Hello Mr Robert,

Many thanks for your help.

But I have an other issue. 

I have those settings in my model:

set ND ; # DCs location
set F1 {ND} ; # Set of trucks
set F1T {d in ND} within F1[d] ; # Set of internal combustion trucks

And I wrote this statement in the objectif function:

minimize :
sum {d in ND, t in F1T[d], i in ({d} union NS), j in ({d} union NS): i <> j} ((Ze/(Ka*Ps))*Ef*Es*Ed*(D_LINKS_F1 [i,j]/V_F1)*X_F1[d,t,i,j]
+ (1/(1000*Ep*Om))*(0.5*Cd*Fs*Ad)*D_LINKS_F1[i,j]*((V_F1)^2)* X_F1[d,t,i,j]
+ (1/(1000*Ep*Om))*(Gc*sin(Fi)+Gc*Cr*cos(Fi))*(Cw_F1*X_F1[d,t,i,j]+CHARG_F1 [d,t,i,j])*D_LINKS_F1[i,j])*Ce ;

And I have had this syntax error:

syntax error
context:  sum {d in ND, t in F1T[d], i in ({d} union NS), j in ({d} union NS): i <> j}  >>> ((Ze/(Ka*Ps))*Ef*E <<< s*Ed*(D_LINKS_F1 [i,j]/V_F1)*X_F1[d,t,i,j]
ampl: 

Could you help me with this?

Please receive my best regards.

[AMPL Google Group]

I do not see anything in your objective function that is clearly an error, except that you have not given it a name. The name should go between "minimize" and the ":" character.

To get more help, reply with your model file attached. (Actually just a part of the model file, with the objective function and everything before it, should be enough.)

--
Robert Fourer
AMPL Forum

{#HS:2154005722-113993#}

On Wed, Feb 15, 2023 at 6:43 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Hello Mr Robert,

Many thanks for your help.

But I have an other issue.
I have those settings in my model:

set ND ; # DCs location
set F1 {ND} ; # Set of trucks
set F1T {d in ND} within F1[d] ; # Set of internal combustion trucks

And I wrote this statement in the objectif function:

minimize :
sum {d in ND, t in F1T[d], i in ({d} union NS), j in ({d} union NS): i <> j} ((Ze/(Ka*Ps))*Ef*Es*Ed*(D_LINKS_F1 [i,j]/V_F1)*X_F1[d,t,i,j]
+ (1/(1000*Ep*Om))*(0.5*Cd*Fs*Ad)*D_LINKS_F1[i,j]*((V_F1)^2)* X_F1[d,t,i,j]
+ (1/(1000*Ep*Om))*(Gc*sin(Fi)+Gc*Cr*cos(Fi))*(Cw_F1*X_F1[d,t,i,j]+CHARG_F1 [d,t,i,j])*D_LINKS_F1[i,j])*Ce ;

And I have had this syntax error:

syntax error
context: sum {d in ND, t in F1T[d], i in ({d} union NS), j in ({d} union NS): i <> j} >>> ((Ze/(Ka*Ps))*Ef*E <<< s*Ed*(D_LINKS_F1 [i,j]/V_F1)*X_F1[d,t,i,j]
ampl:

Could you help me with this?

Please receive my best regards.

On Tue, Feb 14, 2023 at 6:19 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

You can read the sets F1T and F1H using indexed collections of tables:

table setF1T {d in ND} IN "amplxl" "setofsets2.xlsx":
   F1T[d] <- [(d)];
read table setF1T;

table setF1H {d in ND} IN "amplxl" "setofsets2.xlsx":
   F1H[d] <- [(d)];
read table setF1H;

Reading the parameters is then straightforward:

table CdFsEf IN "amplxl" "setofsets2.xlsx":
   [ND,F1], Cd, Fs, Ef;
read table CdFsEf;

You can test this out using the attached script and spreadsheet.

--
Robert Fourer
AMPL Forum

[Vivi]

Dear Mr Fourer,

I have put the model as well as the run file, spreadsheet files for data on your private folder you previously provided me (https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv).

Many thanks in advance for your help.

[AMPL Google Group]

You define param Ef to be indexed over two sets:

param Ef {d in ND, t in (F1T[d] union F1H[d])} >= 0 ;

So in the expression for your objective function, you need to replace all occurrences of "Ef" by "Ef[d,t]". There is also the same problem with other parameters in the objective function.

--
Robert Fourer
AMPL Forum

{#HS:2154005722-113993#}

On Thu, Feb 16, 2023 at 6:11 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Mr Fourer,

I have put the model as well as the run file, spreadsheet files for data on your private folder you previously provided me (https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv).
Many thanks in advance for your help.

On Thu, Feb 16, 2023 at 5:10 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

I do not see anything in your objective function that is clearly an error, except that you have not given it a name. The name should go between "minimize" and the ":" character.

To get more help, reply with your model file attached. (Actually just a part of the model file, with the objective function and everything before it, should be enough.)

--
Robert Fourer
AMPL Forum

[Vivi]

Dear Robert,

The error for the previous request I made to you was due to the no indexing on parameters Ef as you noticed.

But I have an other issue now.

I have the following error notification:

Error at _cmdno 13 executing "read_table" command
(file C:\MltO_5_10_m2_v1.run, line 75, offset 2180):

    Error reading table setF1T['DC1'] with table handler amplxl:
    could not find a range or sheet named setF1T

I have uploaded the model, run file, spreadsheet files for data on your private folder:

https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv

Many thanks in advance for your help.

[AMPL Google Group]

The error "could not find a range or sheet named setF1T" means that you did not tell AMPL where the table setF1T is located in the spreadsheet.

Since you have several tables on the same sheet, the easiest was to fix this is to define a name for each table where a range is located. In this example, you would select the range B24:C29 in 5_10_Param_veh.xlsx, and then where B24 appears in the box at the upper left, you would replace it with setF1T.

--
Robert Fourer
AMPL Forum

{#HS:2154005722-113993#}

On Sat, Feb 18, 2023 at 12:08 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Robert,

The error for the previous request I made to you was due to the no indexing on parameters Ef as you noticed.
But I have an other issue now.

I have the following error notification:

Error at _cmdno 13 executing "read_table" command
(file C:\MltO_5_10_m2_v1.run, line 75, offset 2180):

Error reading table setF1T['DC1'] with table handler amplxl:
could not find a range or sheet named setF1T

I have uploaded the model, run file, spreadsheet files for data on your private folder:
https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv

Many thanks in advance for your help.

On Thu, Feb 16, 2023 at 9:24 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

You define param Ef to be indexed over two sets:

param Ef {d in ND, t in (F1T[d] union F1H[d])} >= 0 ;

So in the expression for your objective function, you need to replace all occurrences of "Ef" by "Ef[d,t]". There is also the same problem with other parameters in the objective function.

--
Robert Fourer
AMPL Forum

[Vivi]

Dear Mr Fourer,

Many Thanks for your help.

I could like to aks you how to write in the result for the variable below in spreadsheet file:

display 

{d in ND, t in F1[d], i in ({d} union NS), j in ({d} union NS): i<>j and X_F1[d,t,i,j]=1} X_F1[d,t,i,j]

Best regards.

[AMPL Google Group]

You would use a table statement like this:

table X_F1out OUT "amplxl" "Xresults.xlsx":
   {d in ND, t in F1[d], i in ({d} union NS), j in ({d} union NS):
         i<>j and X_F1[d,t,i,j]=1} -> [Dval,Tval,Ival,Jval],
      X_F1[d,t,i,j];

Note that [Dval,Tval,Ival,Jval] just gives some names for the columns of the spreadsheet table; they are not names from the model, and you can replace them by whatever column names you want to have. There's an explanation of this form of table statement in Section 10.4 Writing Data of the AMPL book, particularly in the sub-section on Writing rows inferred from a key specification.

--
Robert Fourer
AMPL Forum

{#HS:2154005722-113993#}

On Mon, Feb 20, 2023 at 7:49 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Mr Fourer,

Many Thanks for your help.
I could like to aks you how to write in the result for the variable below in spreadsheet file:

display
{d in ND, t in F1[d], i in ({d} union NS), j in ({d} union NS): i<>j and X_F1[d,t,i,j]=1} X_F1[d,t,i,j]

On Sun, Feb 19, 2023 at 3:05 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

The error "could not find a range or sheet named setF1T" means that you did not tell AMPL where the table setF1T is located in the spreadsheet.

Since you have several tables on the same sheet, the easiest was to fix this is to define a name for each table where a range is located. In this example, you would select the range B24:C29 in 5_10_Param_veh.xlsx, and then where B24 appears in the box at the upper left, you would replace it with setF1T.

--
Robert Fourer
AMPL Forum

On Sat, Feb 18, 2023 at 12:08 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Robert,

The error for the previous request I made to you was due to the no indexing on parameters Ef as you noticed.
But I have an other issue now.

I have the following error notification:

Error at _cmdno 13 executing "read_table" command
(file C:\MltO_5_10_m2_v1.run, line 75, offset 2180):

Error reading table setF1T['DC1'] with table handler amplxl:
could not find a range or sheet named setF1T

I have uploaded the model, run file, spreadsheet files for data on your private folder:
https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv

Many thanks in advance for your help.

On Thu, Feb 16, 2023 at 9:24 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

You define param Ef to be indexed over two sets:

param Ef {d in ND, t in (F1T[d] union F1H[d])} >= 0 ;

So in the expression for your objective function, you need to replace all occurrences of "Ef" by "Ef[d,t]". There is also the same problem with other parameters in the objective function.

--
Robert Fourer
AMPL Forum

On Thu, Feb 16, 2023 at 6:11 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Mr Fourer,

I have put the model as well as the run file, spreadsheet files for data on your private folder you previously provided me (https://www.dropbox.com/request/Yy6cQ0RGwqCsBy9Kr7vv).

Many thanks in advance for your help.

[Vivi]

Hello Mr Fourer,

Many thanks for your help!

Best regards.