Originally posted by: Striker121

Hi there,

 

I would greatly appreciate any help on the following issue I am encountering. I am running IBM ILOG CPLEX Optimization Studio Version: 12.7.0.0 Build id: 0.

 

My MILP requires the following expression as part of a constraint (see sheet 3 of the attached Excel spreadsheet for the mathematical expression):

subject to{

      

       forall (i in BatchI, s in S[i])

       sum (j in J[i][s]) Y[i][j] == 1 ;

}

I have attached an Excel file, ExampleData.xls, which contains some dummy data for the arrays J[i][s] (in Sheet1) and S[i] (in Sheet2). Each element in the array J[i][s] is to be a set of numbers. For example, from the Excel file, J[1][2] should equal the set [1, 2, 9] and J[3][3] should equal the set [2]. Similarly, each element in the S[i] array is to be a set of numbers. From the Excel data file, S[2] should equal the set [2,3,4] and S[3] should equal the set [1,2,3].

I am having difficulty in creating the J[i][s] and S[i] arrays and having them read their data from the Excel file. Is there any way this can be achieved?

When I run the configuration, I get an error when reading the ExampleData.xls "Data element "J" of type {int}[BatchI][StagesS] not supported for sheets.".

 

Below is my code from the .mod file I have written:

 

intnbBatchesI = ...;

intnbStagesS = ...;

 

rangeBatchI= 1..nbBatchesI;

rangeStagesS= 1..nbStagesS;

 

dvar booleanY[BatchI][StagesS];

 

{int}J[BatchI][StagesS] = ...;

{int} S[BatchI] = ...;

 

 

subject to{

      

       forall (i in BatchI, s in S[i])

       sum (j in J[i][s]) Y[i][j] == 1 ;

}

 

 

 

Below is my code from the .dat file I have written:

 

nbBatchesI= 3;

nbStagesS= 3;

 

SheetConnection Example_Data("ExampleData.xls");

 

J from SheetRead(Example_Data, "'Sheet1'!A1:C3");

 

S from SheetRead(Example_Data, "'Sheet2'!A1:C1");

I have attached all files below.

Again, I would be very grateful for any help you may offer.

 

Thank you.

 

 

 

 

 

 

 

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

I would use some scripting in order to convert strings into sets:

.mod

/*********************************************
 * OPL 12.7.0.0 Model
 * Author: Brian
 * Creation Date: 19 Jul 2017 at 11:01:38
 *********************************************/
 
int nbBatchesI = ...;
int nbStagesS = ...;
 
range BatchI= 1..nbBatchesI;
range StagesS= 1..nbStagesS;

dvar boolean Y[BatchI][StagesS];

string stJ[BatchI][StagesS]=...;
string stS[BatchI] = ...;

{int} J[BatchI][StagesS];
{int} S[BatchI];

execute convert_strings
{
for(var b in BatchI)
{

   for(var s in StagesS)
   {
     var ar=stJ[b][s].split(",");
     for(var i=0;i<ar.length;i++) J[b][s].add(Opl.intValue(ar[i]));   
   }

   var ar=stS[b].split(",");
   for(var i=0;i<ar.length;i++) S[b].add(Opl.intValue(ar[i]));
}
}

subject to {
    
    forall (i in BatchI, s in S[i]: s in StagesS)
    sum (j in J[i][s]:j in StagesS) Y[i][j] == 1 ;
}

 

.dat

nbBatchesI = 3;
nbStagesS = 3;

SheetConnection Example_Data ("ExampleData.xls");

stJ from SheetRead(Example_Data, "'Sheet1'!A1:C3");

stS from SheetRead(Example_Data, "'Sheet2'!A1:C1");

regards

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Striker121

Hi Alex,

 

Thank you for your reply. 

 

I note you added ":s in StagesS". Should I read this as "such that s is also a member of the set StagesS"? In the example attached above therefore, s will either be 1, 2, or 3.

 

Furthermore, is the reason you did this for good coding practice so to speak? In other words, it ensures that any of the s in S[i] are also members of the set StagesS (but it isn't strictly necessary to include it)? Or is there any other reason for this?

 

I also note the addition of ":j in StagesS" in the constraint. In my initial example, I forgot to mention that j is the index of a machine in a particular stage of a production line. Consequently, there may be more machines than stages. In other words, the maximum value of j could be greater than the largest value in the range StagesS. If you consider my initial dummy data, J[1][1] reads [1 3 5]. By including ": j in StagesS", would it limit the maximum value j can take to the maximum value in the range StagesS, which in this case is 3? The maximum value j should have taken when reading J[1][1] is actually 5. As such, I should remove the ":j in StagesS", if not it will incorrectly limit the value j can take? Or was there another reason for its inclusion? 

 

Should I instead define a new range, "range MachinesJ = 1..6;" (as 6 is the total number of machines in the system, see Sheet1 of spreadsheet) and then replace ":j in StagesS" with ":j in MachinesJ "?

 

 

Many thanks for your help.

#DecisionOptimization
#OPLusingCPLEXOptimizer

Hi,

indeed I changed the constraints in order to avoid out of range errors.

But

/*********************************************
 * OPL 12.7.0.0 Model
 * Author: Brian
 * Creation Date: 19 Jul 2017 at 11:01:38
 *********************************************/
 
int nbBatchesI = ...;
int nbStagesS = ...;
 
range BatchI= 1..nbBatchesI;
range StagesS= 1..nbStagesS;

dvar boolean Y[BatchI][StagesS];

string stJ[BatchI][StagesS]=...;
string stS[BatchI] = ...;

{int} J[BatchI][StagesS];
{int} S[BatchI];

execute convert_strings
{
for(var b in BatchI)
{

   for(var s in StagesS)
   {
     var ar=stJ[b][s].split(",");
     for(var i=0;i<ar.length;i++) J[b][s].add(Opl.intValue(ar[i]));   
   }

   var ar=stS[b].split(",");
   for(var i=0;i<ar.length;i++) S[b].add(Opl.intValue(ar[i]));
}
}

execute
{
writeln("J=",J);
writeln("S=",S);
}

subject to {
    
//    forall (i in BatchI, s in S[i])
//    sum (j in J[i][s]) Y[i][j] == 1 ;
}

would give

J= [[{1 3 5} {1 2 9} {1 2}]
         [{2} {2 5 6} {1 3 5 6}]
         [{} {1 2 5} {2}]]
S= [{1 2 3 4} {2 3 4} {1 2 3}]

regards

 

PS:

More links at https://www.ibm.com/developerworks/community/forums/html/topic?id=0d0b2396-3b48-4638-b032-3b9ea74f1a11&ps=25

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Striker121

Hi Alex,

 

Many thanks for your help! I greatly appreciate it.

 

Cheers!

#DecisionOptimization
#OPLusingCPLEXOptimizer