[AMPL 24531] Write spreadsheet file with indexed set

Google Group Mirror
1

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.

2

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.

(Attachment setofsets2.txt is missing)

(Attachment setofsets2.xlsx is missing)

3

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/(KaPs))EfEsEd*(D_LINKS_F1 [i,j]/V_F1)*X_F1[d,t,i,j]

  • (1/(1000EpOm))(0.5CdFsAd)D_LINKS_F1[i,j]((V_F1)^2)* X_F1[d,t,i,j]
  • (1/(1000EpOm))(Gcsin(Fi)+GcCrcos(Fi))(Cw_F1X_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/(KaPs))EfE <<< sEd*(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.

4

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.)

5

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 (Submit files).
Many thanks in advance for your help.

6

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.

7

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.

8

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.

9

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.

10

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.

11

Hello Mr Fourer,

Many thanks for your help!

Best regards.