AMPL Direct Spreadsheet Interface: Iteration problem

100 views
Skip to first unread message

cielocr...@gmail.com

unread,
Nov 12, 2021, 7:02:34 PM11/12/21
to AMPL Modeling Language
I am trying the xlsx interface in AMPL. I am running a sensitivity analysis over my model, in which I iterate over a list of parameters. My current script generates a txt file that stores individual files of the solutions of each iteration. After several failed attempts  I would like to know IF it is possible to do this with tables. I am able to get the table results for a single instance, using the script:

table Results_ OUT "amplxl" "Instance_1.xlsx" "Results":
 [TIME],  {t in TIME} sum {j in STAFF, d in DEPARTMENTS} I[t,j,d] ~ I; 
 write table Results  ;

So, I would like to get an individual table for each iteration. 

The current code has the form: 

param file_instance symbolic default "instance.txt";

param total_instances default card(valores_sens_e)*card(valores_sens_q)*card(valores_sens_th)*card(valores_sens_test);
param cont_instance default 1;

let archivo_instancia := sprintf("solucion_sensE%s_sensQ%s_sensTH%s_sensTT%s.txt", sens_e, sens_q, sens_th, sens_test);

# writing the instances
display  ALL.val > (file_instance); 
display X.val  >  (file_instance); 
display Y.val  > (file_instance); 
etc....

close file_instance;
Thank you in advance for the support!

Message has been deleted

cielocr...@gmail.com

unread,
Nov 14, 2021, 5:54:20 PM11/14/21
to AMPL Modeling Language
UPDATE 
I am including the .run file here. I used the script to generate output on csv files. Now I would like to have excel files as an output for easy data manipulation and analysis. I am not sure how to modify the script to have 1 excel file for each scenario in the sensitivity analysis. I would appreciate the help! 

load amplxl.dll; 
reset;  
model XX.mod;  
data XX.dat;  

option solver knitro; # CPLEX como solver 
option knitro_options 'act_lppenalty=2 presolve=1';
option show_stats 1;

 
# Testing values_sensitivity analysis (This values are defined in the .mod multiplying the parameters for the sensitivity analysis) 
set valores_sens_e := 1..16 by 8;
set valores_sens_q := 0..16 by 8;
set valores_sens_th := 5..20 by 10;
set valores_sens_test := 0.02..0.1 by 0.05;

#Defining the parameters_character strings
param archivo_resumen symbolic default "resumen.txt";
param archivo_instancia symbolic default "instancia.xlsx";

#Defining instances counter
param total_de_instancias default card(valores_sens_e)*card(valores_sens_q)*card(valores_sens_th)*card(valores_sens_test);
param contador_instancia default 1;

printf "Correr %s instancias\n", total_de_instancias; # Number of instances to run
# Esta parte imprime los nombres de las columnas
printf "sens_e\tsens_q\tsens_th\tsens_test" > (archivo_resumen); # cabecera de la tabla resumen: parámetros sensibilizados
#ahora los indicadores
#Officce occupancy 
for{j in STAFF}
{
printf "\tOOS_%s",j > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tOOD_%s",d  > (archivo_resumen);
}

#Home occupancy
for{j in STAFF}
{
printf "\tHOS_%s",j > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tHOD_%s",d > (archivo_resumen);
}


#Total_Occupancy_OFFICE
printf "\tOO" > (archivo_resumen);
#Total_Occupancy_HOME
printf "\tHO" > (archivo_resumen);
#Inf 
for{j in STAFF}
{
printf "\tIS_%s",j > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tID_%s",d > (archivo_resumen);
}
#Total_Inf
printf  "\tIT" > (archivo_resumen);
#Q
for{j in STAFF}
{
printf "\tQS_%s",j  > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tQD_%s",d > (archivo_resumen);
}
#Total_Q
printf "\tQT" > (archivo_resumen);
#Total_Home
printf "\tTH" > (archivo_resumen);
#Total_Office
printf "\tTO" > (archivo_resumen);
 
        #Objective
        
        printf "\tBEN" > (archivo_resumen);
    
    printf "\n" > (archivo_resumen);
    
# Experiments

----------------------------------------------##Here I am guessing where to include the table definition##---------------------------------------------
table W2Results OUT "amplxl" "W2.xlsx" "Allocation_office":
  {j in STAFF} -> [staff],  {d in DEPARTMENTS}  <X[j,d]  ~  ("Department" & d)>  ;
 write table W2Results;

table W2Results2 OUT "amplxl" "W2.xlsx" "Allocation_home":
  {j in STAFF} -> [staff],  {d in DEPARTMENTS}  <Y[j,d]  ~  ("Department" & d)>  ;
 write table W2Results2;


 
table W2Results3 OUT "amplxl" "W2.xlsx" "Contacts_D":
  {j in STAFF} -> [staff],  {d in DEPARTMENTS}  <k[j,d]  ~  ("Department" & d)>  ;
 write table W2Results3;
  
table W2Results4 OUT "amplxl" "W2.xlsx" "Inf, Test":
 [TIME],  {t in TIME} (sum {j in STAFF, d in DEPARTMENTS} I[t,j,d] ~ Inf, sum {j in STAFF, d in DEPARTMENTS} Q[t,j,d] ~ Test)  ; 
write table W2Results4;  
 

 
table W2Results5 OUT "amplxl" "W2.xlsx" "Allocation_office_home_inf":
 [], ALL.val ~ Objective, sum {j in STAFF, d in DEPARTMENTS} X[j,d] ~ Total_office, sum {j in STAFF, d in DEPARTMENTS} Y[j,d] ~ Total_home,
 1/15*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} I[t,j,d] ) ~ InfA ;  
write table W2Results5;

 
 
table W2Results6 OUT "amplxl" "W2.xlsx" "Inf_D":
  {t in TIME} -> [time],  { d in DEPARTMENTS}  <sum {j in STAFF} I[t,j,d]  ~  ("Department" & d)>  ;
 write table W2Results6;
  

table W2Results7 OUT "amplxl" "W2.xlsx" "Inf_S":
  {t in TIME} -> [time],  { j in STAFF}  <sum {d in DEPARTMENTS} I[t,j,d]  ~  ("Staff" & j)>  ;
 write table W2Results7;
 


table W2Results8  {j in STAFF} OUT "amplxl" "W2.xlsx"  ("Istaff" & j) :
  [TIME], {d in DEPARTMENTS} < {t in TIME} I[t,j,d]  ~  (d)>  ;
 write table W2Results8;
 
  
table W2Results9  {j in STAFF} OUT "amplxl" "W2.xlsx"  ("Qstaff" & j) :
  [TIME], {d in DEPARTMENTS} < {t in TIME} Q[t,j,d]  ~  (d)>  ;
 write table W2Results9;
  
 ----------------------------------------------##Here I am guessing where to include the table definition##---------------------------------------------
  
#defining the loop for the experiments
for {tar in valores_sens_e, cap in valores_sens_q, wei in valores_sens_th, tei in valores_sens_test} 
{
# Mostrar cosas por pantalla
printf "\n\nInstancia %s de %s (%3.1f\%)\n", contador_instancia, total_de_instancias, (contador_instancia/total_de_instancias)*100;
printf "sens_e\t%s\n", tar;
printf "sens_q\t%s\n", cap;
printf "sens_th\t%s\n", wei;
printf "sens_test\t%s\n", tei;
printf "\n";
# Parameters_change
let sens_e := tar;
let sens_q := cap;
let sens_th := wei;
let sens_test := tei;
 
solve;  
# name of the output file #I used this script for csv output file
let archivo_instancia := sprintf("solucion_sensE%s_sensQ%s_sensTH%s_sensTT%s.txt", sens_e, sens_q, sens_th, sens_test);

# escribir archivo de instancia
 ----------------------------------------------##This block write the solutions to "archivo_instancia" 1 file by experiment##---------------------------------------------
   #I would like to write the tables defined above to archivo_instancia


#display  ALL.val > (archivo_instancia); # Muestra el valor de la FO
#display X.val  > (archivo_instancia); 
#display Y.val  > (archivo_instancia);
#display k.val  > (archivo_instancia); 
#display I.val  > (archivo_instancia); 
#display Q.val  > (archivo_instancia); 
#display (1/T*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} I[t,j,d] ))  > (archivo_instancia);
#display (1/T*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} Q[t,j,d] ))   > (archivo_instancia);
#display sum {j in STAFF, d in DEPARTMENTS} X[j,d]  > (archivo_instancia);
#display sum {j in STAFF, d in DEPARTMENTS} Y[j,d]  > (archivo_instancia);
#display {t in TIME, d in DEPARTMENTS} (sum {j in STAFF} I[t,j,d])  > (archivo_instancia);
#display {t in TIME} (sum {j in STAFF, d in DEPARTMENTS} I[t,j,d]) > (archivo_instancia);

 ----------------------------------------------##This block write the solutions to "archivo_instancia" 1 file by experiment##---------------------------------------------
  
# cerrar el archivo de salida
close archivo_instancia;
# Comienzo a escribir la fila de la tabla resumen para esta instancia
printf "%s\t%s\t%s\t%s", sens_e, sens_q, sens_th, sens_test > (archivo_resumen);
#INDICADORES (caso factible)
if solve_result = "solved" then
{
#Officce occupancy 
for{j in STAFF}
{
printf "\t%s",((sum {d in DEPARTMENTS} X[j,d]) / (sum {d in DEPARTMENTS} (n[j,d]))) > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\t%s", (((sum {j in STAFF} X[j,d])) / (sum {j in STAFF} (n[j,d]))) > (archivo_resumen);
}
#Home occupancy 
for{j in STAFF}
{
printf "\t%s",((sum {d in DEPARTMENTS} Y[j,d]) / (sum {d in DEPARTMENTS} (n[j,d]))) > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\t%s", ((sum {j in STAFF} Y[j,d]) / (sum {j in STAFF} (n[j,d]))) > (archivo_resumen);
}
#Total_Occupancy_OFFICE
printf "\t%s", ((sum {j in STAFF, d in DEPARTMENTS} X[j,d]) / (sum {j in STAFF, d in DEPARTMENTS} (n[j,d]))) > (archivo_resumen);
#Total_Occupancy_HOME
printf "\t%s", ((sum {j in STAFF, d in DEPARTMENTS} Y[j,d]) / (sum {j in STAFF, d in DEPARTMENTS} (n[j,d])))  > (archivo_resumen);
#Inf
for{j in STAFF}
{
printf "\t%s", (1/T*(sum {t in TIME, d in DEPARTMENTS} I[t,j,d] ))  > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\t%s", (1/T*(sum {t in TIME, j in STAFF} I[t,j,d] ))  > (archivo_resumen);
}

#Total_Inf
printf "\t%s", (1/T*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} I[t,j,d] ))   > (archivo_resumen);
#Q
for{j in STAFF}
{
printf "\t%s", (1/15*(sum {t in TIME, d in DEPARTMENTS} Q[t,j,d] ))  > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\t%s", (1/15*(sum {t in TIME, j in STAFF} Q[t,j,d] ))  > (archivo_resumen);
}

#Total_Q
printf "\t%s", (1/15*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} Q[t,j,d] ))   > (archivo_resumen);
#Total_HOME
printf "\t%s", (sum{j in STAFF, d in DEPARTMENTS} Y[j,d])   > (archivo_resumen);
#Total_OFFICE
printf "\t%s",  (sum{j in STAFF, d in DEPARTMENTS} X[j,d])   > (archivo_resumen);
      #Objective
   
    printf "\t%s", sum{j in STAFF, d in DEPARTMENTS} X[j,d] > (archivo_resumen);

    }
    
#INDICADORES (caso infactible)
else if solve_result = "infeasible" then
{
#Officce occupancy 
for{j in STAFF}
{
printf "\tinfeasible" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tinfeasible" > (archivo_resumen);
}

#Home occupancy
for{j in STAFF}
{
printf "\tinfeasible" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tinfeasible" > (archivo_resumen);
}


#Total_Occupancy_OFFICE
printf "\tinfeasible" > (archivo_resumen);
#Total_Occupancy_HOME
printf "\tinfeasible" > (archivo_resumen);
#Inf
for{j in STAFF}
{
printf "\tinfeasible" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tinfeasible" > (archivo_resumen);
}
#Total_Inf
printf "\tinfeasible" > (archivo_resumen);
#Q
for{j in STAFF}
{
printf "\tinfeasible" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tinfeasible" > (archivo_resumen);
}
#Total_Q
printf "\tinfeasible" > (archivo_resumen);
#Total_Home
        
        printf "\tinfeasible" > (archivo_resumen);
        
        #OTotal_Office
        
        printf "\tinfeasible" > (archivo_resumen);
 
        #Objective
        
        printf "\tinfeasible" > (archivo_resumen);
    
    
    }

#INDICADORES (caso que no es óptimo ni infactible a.k.a. "problem!")
else
{
#Officce occupancy 
for{j in STAFF}
{
printf "\tproblem!" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tproblem!" > (archivo_resumen);
}

#Home occupancy
for{j in STAFF}
{
printf "\tproblem!" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tproblem!" > (archivo_resumen);
}


#Total_Occupancy_OFFICE
printf "\tproblem!" > (archivo_resumen);
#Total_Occupancy_HOME
printf "\tproblem!" > (archivo_resumen);
#Inf
for{j in STAFF}
{
printf "\tproblem!" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tproblem!" > (archivo_resumen);
}
#Total_Inf
printf "\tproblem!" > (archivo_resumen);
#Q
for{j in STAFF}
{
printf "\tproblem!" > (archivo_resumen);
}
for{d in DEPARTMENTS}
{
printf "\tproblem!" > (archivo_resumen);
}
#Total_Q
printf "\tproblem!" > (archivo_resumen);
#Total_Home
        printf "\tproblem!" > (archivo_resumen);
        
        #Total_Office
        
        printf "\tproblem!" > (archivo_resumen);
 
       #Objective
        
        printf "\tproblem!" > (archivo_resumen);
    
    
    
    }
printf "\n" > (archivo_resumen);
let contador_instancia := contador_instancia + 1;
}

close archivo_resumen; 

AMPL Google Group

unread,
Nov 15, 2021, 11:14:40 AM11/15/21
to AMPL Modeling Language
Here is a general approach you can follow: Before the loop that iterates through the scenarios, define an indexed collection of tables:

table W2Results {s in SCENARIOS} OUT "amplxl" ("W2" & s & ".xlsx") "Contacts_D":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <k[j,d] ~ ("Department" & d)> ;

Then inside the loop, write a different table each time:

for {s in SCENARIOS} {
   ...
   solve;
   write table W2Results[s];
}

When "write table W2Results[s]" is executed, the output goes to spreadsheet file ("W2" & s & ".xlsx"), which is different for each scenario s.

Of course this is only an illustration of the general approach, which uses an indexed collection of tables together with a string expression to define the name of the spreadsheet file for each table. You will need to adapt this approach to your particular application.


--
Robert Fourer
am...@googlegroups.com
{#HS:1695474340-107143#}
On Sun, Nov 14, 2021 at 10:29 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
UPDATE

I have defined the Output of my tables, and it works OK for one instance,
it generates 1 excel file (W2.xlsx) with all the tables. I am not sure how
to write the loop for obtaining 1 excel file for each run in the
sensitivity analysis.

The tables:

solve;


table W2Results OUT "amplxl" "W2.xlsx" "Allocation_office":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <X[j,d] ~ ("Department" &
d)> ;
write table W2Results;

table W2Results2 OUT "amplxl" "W2.xlsx" "Allocation_home":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <Y[j,d] ~ ("Department" &
d)> ;
write table W2Results2;


table W2Results3 OUT "amplxl" "W2.xlsx" "Contacts_D":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <k[j,d] ~ ("Department" &
d)> ;
write table W2Results3;

table W2Results4 OUT "amplxl" "W2.xlsx" "Infections, Test":

[TIME], {t in TIME} (sum {j in STAFF, d in DEPARTMENTS} I[t,j,d] ~
Infections, sum {j in STAFF, d in DEPARTMENTS} Q[t,j,d] ~ Test) ;
write table W2Results4;



table W2Results5 OUT "amplxl" "W2.xlsx" "Allocation_office_home_infections":

[], ALL.val ~ Objective, sum {j in STAFF, d in DEPARTMENTS} X[j,d] ~
Total_office, sum {j in STAFF, d in DEPARTMENTS} Y[j,d] ~ Total_home,
1/15*(sum {t in TIME, j in STAFF, d in DEPARTMENTS} I[t,j,d] ) ~
InfectionsA ;
write table W2Results5;



table W2Results6 OUT "amplxl" "W2.xlsx" "Infections_D":

{t in TIME} -> [time], { d in DEPARTMENTS} <sum {j in STAFF} I[t,j,d]
~ ("Department" & d)> ;
write table W2Results6;


table W2Results7 OUT "amplxl" "W2.xlsx" "Infections_S":

{t in TIME} -> [time], { j in STAFF} <sum {d in DEPARTMENTS} I[t,j,d]
~ ("Staff" & j)> ;
write table W2Results7;



table W2Results8 {j in STAFF} OUT "amplxl" "W2.xlsx" ("Istaff" & j) :
[TIME], {d in DEPARTMENTS} < {t in TIME} I[t,j,d] ~ (d)> ;
write table W2Results8;


table W2Results9 {j in STAFF} OUT "amplxl" "W2.xlsx" ("Qstaff" & j) :
[TIME], {d in DEPARTMENTS} < {t in TIME} Q[t,j,d] ~ (d)> ;
write table W2Results9;
---------------------------------

Thanks in advance!

On Sat, Nov 13, 2021 at 12:02 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
I am trying the xlsx interface in AMPL. I am running a sensitivity analysis over my model, in which I iterate over a list of parameters. My current script generates a txt file that stores individual files of the solutions of each iteration. After several failed attempts I would like to know IF it is possible to do this with tables. I am able to get the table results for a single instance, using the script:

table Results_ OUT "amplxl" "Instance_1.xlsx" "Results":
[TIME], {t in TIME} sum {j in STAFF, d in DEPARTMENTS} I[t,j,d] ~ I;
write table Results ;

So, I would like to get an individual table for each iteration.

The current code has the form:

param file_instance symbolic default "instance.txt";

param total_instances default card(valores_sens_e)*card(valores_sens_q)*card(valores_sens_th)*card(valores_sens_test);
param cont_instance default 1;

let archivo_instancia := sprintf("solucion_sensE%s_sensQ%s_sensTH%s_sensTT%s.txt", sens_e, sens_q, sens_th, sens_test);

# writing the instances
display ALL.val > (file_instance);
display X.val > (file_instance);
display Y.val > (file_instance);
etc....

close file_instance;
Thank you in advance for the support!

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ampl/333d3277-e7ac-4001-9b3c-0c7b3ff60f52n%40googlegroups.com.

Message has been deleted

cielocr...@gmail.com

unread,
Nov 16, 2021, 4:22:04 AM11/16/21
to AMPL Modeling Language
Dear Robert, 

Thank you for your answer, it worked properly! Now I am trying to define a three-dimensional table under scenarios. How can I define the table for this case? 

My guess, but it is not working :  

 table W2Results {a in SCENARIOS} {j in STAFF} OUT "amplxl" ("W2" & a & ".xlsx") ("Istaff" & j) :
[TIME], {d in DEPARTMENTS} < {t in TIME} I[t,j,d] ~ (d)> ;
Thanks in advance for your help!

cielocr...@gmail.com

unread,
Nov 16, 2021, 6:37:54 AM11/16/21
to AMPL Modeling Language
I also would like to know if it is possible to format the output file name of each scenario with "printf" or "sprintf". 

My guess, but not working: 

 table W2Results {tar in valores_sens_e, cap in valores_sens_q, wei in valores_sens_th, tei in valores_sens_test}  OUT "amplxl" (printf("Sens%d" & tar   & " " cap  & " "  wei & " " tei & "W2.xlsx")) "Allocation_office":
  {j in STAFF} -> [staff],  {d in DEPARTMENTS}  <X[j,d]  ~  ("Department" & d)>  ;

TIA! 

AMPL Google Group

unread,
Nov 17, 2021, 12:22:21 PM11/17/21
to AMPL Modeling Language
I would first try using & operators to form the AMPL string expression in the table statement. For example:

table W2Results {tar in valores_sens_e, cap in valores_sens_q,
   wei in valores_sens_th, tei in valores_sens_test} OUT "amplxl"
("Sens " & tar & " " & cap & " " & wei & " " & tei & " W2.xlsx")
   "Allocation_office": ...

If tar, cap, wei, and tei are numbers (as in the example that you gave) then when the string expression is evaluated, they will be automatically converted to the shortest possible string representations of those numbers.

If you need specify a particular form for the numbers that is different from the automatic formatting, then use sprintf. works exactly like printf except that it is a function that returns a string value (whereas printf is a command that sends it output to the console or a file). For example, if all of the index sets are sets of numbers, here's an example of the third line above using sprintf:

(sprintf("Sens %03d %03d %03d %03d W2.xlsx", tar, cap, wei, tei))


--
Robert Fourer
am...@googlegroups.com
{#HS:1695474340-107143#}
On Tue, Nov 16, 2021 at 11:38 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
I also would like to know if it is possible to format the output file name of each scenario with "printf" or "sprintf".

My guess, but not working:

table W2Results {tar in valores_sens_e, cap in valores_sens_q, wei in valores_sens_th, tei in valores_sens_test} OUT "amplxl" (printf("Sens%d" & tar & " " cap & " " wei & " " tei & "W2.xlsx")) "Allocation_office":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <X[j,d] ~ ("Department" & d)> ;

TIA!

On Tue, Nov 16, 2021 at 9:22 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
Dear Robert,

Thank you for your answer, it worked properly! Now I am trying to define a three-dimensional table under scenarios. How can I define the table for this case?

My guess, but it is not working :

table W2Results {a in SCENARIOS} {j in STAFF} OUT "amplxl" ("W2" & a & ".xlsx") ("Istaff" & j) :
[TIME], {d in DEPARTMENTS} < {t in TIME} I[t,j,d] ~ (d)> ;
Thanks in advance for your help!

On Mon, Nov 15, 2021 at 7:06 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
Dear Robert,

Thank you for the answer. In this statement:


table W2Results {s in SCENARIOS} OUT "amplxl" ("W2" & s & ".xlsx") "Contacts_D": {j in STAFF} -> [staff], {d in DEPARTMENTS} <k[j,d] ~ ("Department" & d)> ;

you indicate "s in SCENARIOS", in my case I have several parameters that move altogether, like in this case:

table W2Results2 {tar in valores_sens_e, cap in valores_sens_q, wei in valores_sens_th, tei in valores_sens_test} OUT "amplxl" "W2.xlsx" "Allocation_home":

{j in STAFF} -> [staff], {d in DEPARTMENTS} <Y[j,d] ~ ("Department" & d)> ; write table W2Results2;

So, for the loop, do I need to indicate each of the parameters that are moving again? Will this generate, 1 excel file with several sheets for the tables?

On Mon, Nov 15, 2021 at 3:48 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:
Here is a general approach you can follow: Before the loop that iterates through the scenarios, define an indexed collection of tables:

table W2Results {s in SCENARIOS} OUT "amplxl" ("W2" & s & ".xlsx") "Contacts_D":
{j in STAFF} -> [staff], {d in DEPARTMENTS} <k[j,d] ~ ("Department" & d)> ;

Then inside the loop, write a different table each time:

for {s in SCENARIOS} {
   ...
   solve;
   write table W2Results[s];
}

When "write table W2Results[s]" is executed, the output goes to spreadsheet file ("W2" & s & ".xlsx"), which is different for each scenario s.

Of course this is only an illustration of the general approach, which uses an indexed collection of tables together with a string expression to define the name of the spreadsheet file for each table. You will need to adapt this approach to your particular application.


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

cielocr...@gmail.com

unread,
Nov 17, 2021, 1:45:02 PM11/17/21
to AMPL Modeling Language
Thank you for your answer! I will try it out! 

Ana

Message has been deleted

AMPL Google Group

unread,
Nov 18, 2021, 7:19:05 PM11/18/21
to AMPL Modeling Language
It looks like you have one left parenthesis but two right parentheses:

("Sens" & tar & cap & wei & tei >>> )) <<< ;

You can add underscores to the filename if you like. For example,

("Sens" & tar & "_" & cap & "_" & wei & "_" & tei)

Definitely you do not want to put your table statement inside a loop, because you only want the table to be defined once. (You can use read table or write table in a loop, however.)


--
Robert Fourer
am...@googlegroups.com
{#HS:1695474340-107143#}
On Thu, Nov 18, 2021 at 10:54 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
Dear Robert, I tried this definition, but it gets a syntax error.

syntax error
context: [TIME], {t in TIME} sum {j in STAFF, d in DEPARTMENTS} Q[t,j,d] ~ ("Sens" & tar & cap & wei & tei >>> )) <<< ;
ampl:

The name of the files is printed in the form: *** Sens1850.01W2*** So, it is difficult to understand the Scenario, I would like something like: **Sens18_5_0.01_W2** or **SensE18_SensQ5_SensTH5_sensTT0.01**. in which I can easily identify the Scenario.

I have tried several ways, including the one you suggested and there is always a syntax error. Should I include this in the for loop?

Best regards,

Ana

On Wed, Nov 17, 2021 at 6:45 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:
Thank you for your answer! I will try it out!

Ana

On Wed, Nov 17, 2021 at 5:21 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:
I would first try using & operators to form the AMPL string expression in the table statement. For example:

table W2Results {tar in valores_sens_e, cap in valores_sens_q,
   wei in valores_sens_th, tei in valores_sens_test} OUT "amplxl"
("Sens " & tar & " " & cap & " " & wei & " " & tei & " W2.xlsx")
   "Allocation_office": ...

If tar, cap, wei, and tei are numbers (as in the example that you gave) then when the string expression is evaluated, they will be automatically converted to the shortest possible string representations of those numbers.

If you need specify a particular form for the numbers that is different from the automatic formatting, then use sprintf. works exactly like printf except that it is a function that returns a string value (whereas printf is a command that sends it output to the console or a file). For example, if all of the index sets are sets of numbers, here's an example of the third line above using sprintf:

(sprintf("Sens %03d %03d %03d %03d W2.xlsx", tar, cap, wei, tei))


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

anshul chauhan

unread,
Mar 23, 2023, 11:42:41 AM3/23/23
to AMPL Modeling Language
This is the VBA code

Option Explicit

Sub runPSA()

Dim i As Integer
Dim N As Integer

N = 10

Range("Parameters!PSA").Value = 2
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For i = 1 To N
    Application.Calculate
    Application.StatusBar = "Reached iteration" & i
   
    Sheets("PSA results").Cells(i + 5, 1).Value2 = i
    Sheets("PSA results").Range("B5:BB5").Offset(i, 0).Value2 = Sheets("PSA results").Range("B5:BB5").Value2
     
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = False
Range("Parameters!PSA").Value = 1

End Sub



It allows only to plot the iterations on the results sheets but it I cannot see any results reflecting in the values after I run the PSA.

Kindly help

Reply all
Reply to author
Forward
0 new messages