Ho Chi Minh City International University Deterministic Models in Operation Research

Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

• File data (*.dat): Make the connection between data store in database file, write the output to the
database file

• File Excel (*.xlsx): Store the data for CPLEX program.

06 Miniguide for CPLEX Usage
• File Text (*.txt): Store the interpretation output of CPLEX program.

Phan Nguyen Ky Phuc • These files must be put in the same folder.

January 26, 2021 The structure of each file structure is common as:

1.1 The model file (*.mod)

Contents
When writing the file.mod, the program should follow the order below:

1 Structure 1
• Declare the size of each index(keyword: int)
1.1 The model file (*.mod) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
• Declare the range of each index(keyword: range)
1.2 The data file (*.dat) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
• Declare the parameters (keyword: float, int)
1.3 The excel file (*.xlsx) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
• Declare the decision variable (keyword: dvar common types: float, float+,int, int+, boolean)
1.4 The text file (*.txt) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
• Set up PRE SETUP ( keyword: execute common settings : cplex.epgap , cplex.tilim) . In case the size
2 Some useful expressions 3 of the problem is too large, this setting is necessary to find the solution within time limitation.

• Objective function( keyword: maximize, minimize)

3 Example 3

3.1 Example 1: Knapsack Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 • Constraint (keyword: forall)

3.2 Example 2: Assignment Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 • Set up POST PROCESSING (keyword: execute)

4 FAQs 6
1.2 The data file (*.dat)
4.1 Question 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
• Connect the database file with the CPLEX program. In case the data is stored in EXCEL file, the
4.2 Question 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
following keyword is used( keyword: SheetConnection).
4.3 Question 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
• Example: SheetConnection Data("ExcelFileNam.xlsx");
4.4 Question 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
• In order to make the data modification is easier, it is recommended that:
4.5 Question 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4.6 Question 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 – All scalar parameters, i.e. parameters have only one value, should be put in a shame sheet.
– All one dimensional data should be put in a shame sheet. Each datum should occupy a column
4.7 Question 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
of sheet
4.8 Question 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
– Two dimensional data should be put in separate sheet.

5 How to use tuple 14 – Example: Capacity from SheetRead(Data,"SheetName !A1: B10");

• Write down the solution to the EXCEL file

1 Structure – Example: X to SheetWrite(Data,"SheetName !A1: B10");

Basically a basic CPLEX project includes 3 file:

• File model (*.mod): Use to describe the model, and declare parameters

1 06 Guide for Cplex Usage Page 2

Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

1.3 The excel file (*.xlsx) Code for CPLEX

File(*.mod) code
• Store Data and Solutions
1 i n t numbType = . . . ; / / d e c l a r e t h e s i z e o f t h e i n d e x
• Excel file can only store directly 2-dimension solutions. 2 r a n g e Type = 1 . . numbType ; / / d e c l a r e t h e r a n g e o f item t y p e
3 f l o a t W[ Type ] = . . . ; / / d e c l a r e t h e w e i g h t p a r a m e t e r s a s s o c i a t i n g with r a n g e Type
• When the dimension of solution is greater than 2. They require to be unfolded to write to the excel 4 f l o a t B [ Type ] = . . . ; / / d e c l a r e t h e b e n e f i t p a r a m e t e r s a s s o c i a t i n g with r a n g e Type
5 f l o a t n [ Type ] = . . . ; / / d e c l a r e t h e max_numb p a r a m e t e r s a s s o c i a t i n g with r a n g e Type
file.
6 f l o a t Cap = . . . ;
7 dvar i n t+ X[ Type ] ; / / d e c l a r e t h e v a r i a b l e
8 e x e c u t e PRE_SETUP {
1.4 The text file (*.txt) 9 c p l e x . epgap = 0 . 1 ;
10 cplex . tilim = 100;
• Store the interpreted results 11 }
12 maximize
13 sum ( i i n Type ) B [ i ] ∗ X[ i ] ;
2 Some useful expressions 14

15 s u b j e c t to {
16 constraint_1 :
• Min and minl: the minimum of several numeric expressions 17 f o r a l l ( i i n Type ) {
18 X[ i ] <= n [ i ] ;
• Max and maxl: the maximum of several numeric expressions 19 }
20 constraint_2 :
• abs: the absolute value of a numeric expression 21 sum ( i i n Type ) W[ i ] ∗ X[ i ] <= Cap ;
22 }
• piecewise: the piecewise linear combination of a numeric expression 23

24 e x e c u t e WRITE_RESULT{
25 v a r o f i l e = new I l o O p l O u t p u t F i l e ( " R e s u l t . t x t " ) ;
3 Example 26 o f i l e . w r i t e l n ( c p l e x . getObjValue ( ) ) ; / / Get t h e v a l u e o f t h e o b j e c t i v e f u n c t i o n
27 f o r ( i i n Type ) {
28 o f i l e . w r i t e l n ( "The v a l u e o f p r o d u c t t y p e [ " , i , " ] : " ,X[ i ] ) ;
3.1 Example 1: Knapsack Problem 29 }
30 o f i l e . w r i t e l n ( "−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−" ) ;
Consider the Knapsack Problem with 4 item types, where the parameters of weight, benefit, and maximum 31 o f i l e . close () ;
32 }
number for each type are given as follow:
Weight={1,3,4,5}, Benefit={3,5,7,9} ,Max Number={1,2,2,1}
File (*.dat) Code
The maximum capacity of the system is 10. Find the maximum benefit that we can achieve
1 S h e e t C o n n e c t i o n MyData ( " KnapsackData . x l s x " ) ;
Mathematical Model
2 numbType from SheetRead ( MyData , " S c a l a r ! B1" ) ;
Size of the problem 3 Cap from SheetRead ( MyData , " S c a l a r ! B2" ) ;
i : index of the item type 4 W from SheetRead ( MyData , " OneDimension ! A2 : A5" ) ;
Parameters 5 B from SheetRead ( MyData , " OneDimension ! B2 : B5" ) ;
6 n from SheetRead ( MyData , " OneDimension ! C2 : C5" ) ;
Bi : the benefit of an item type i
Wi : the weight of an item type i File(*.xlsx)
ni : the maximum quantity of item type i
Cap: maximum capacity of the system
(b) sheet "OneDimension" of "KnapsackData.xlsx"
Decision variables
file
Xi : the number of item type i is adopted (a) sheet "Scalar" of "KnapsackData.xlsx" file
A B C
Objective:
X A B 1 Weight Benefit Max Numb
max Z = Bi Xi
1 Number of Item 4 2 1 3 1
i
2 Max Capacity 10 3 3 5 2
Constrainst: 4 4 7 2
0 ≤ Xi ≤ ni 5 5 9 1
X
Wi Xi ≤ Cap Table 1: Data of Knapsack Problem
∀i

06 Guide for Cplex Usage Page 3 06 Guide for Cplex Usage Page 4
 Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

3.2 Example 2: Assignment Problem 18 constraint_2 :

19 f o r a l l ( j i n Job ) {
Consider the Assignment Problem with 4 workers, 4 jobs.Find the maximum benefit that we can achieve. If 20 sum ( i i n Worker )X[ i ] [ j ]==1;
21 }
the benefit assignment matrix is given follows:
22 }
23 e x e c u t e WRITE_RESULT{
Worker/Job J1 J2 J3 J4
24 v a r o f i l e = new I l o O p l O u t p u t F i l e ( " R e s u l t . t x t " ) ;
W1 1 4 5 0 25 o f i l e . w r i t e l n ( c p l e x . getObjValue ( ) ) ; / / Get t h e v a l u e o f t h e o b j e c t i v e f u n c t i o n
W2 3 2 1 4 26 f o r ( i i n Worker ) f o r ( j i n Job ) {
W3 1 4 5 1 27 i f (X[ i ] [ j ]==1) {
W4 6 2 5 3 28 o f i l e . w r i t e l n ( "Work [ " , i , " ] w i l l be a s s i g n e d t o Job [ " , j , " ] " ) ;
29 }
30 }
31 o f i l e . w r i t e l n ( "−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−" ) ;
The constraints 32 o f i l e . close () ;
33 }
• Every job must be assigned to one worker. 34

• Every worker must be in charge of one job. File (*.dat)

1 S h e e t C o n n e c t i o n MyData ( " a s s g i n m e n t . x l s x " ) ;
Mathematical Programming 2 Numbworker from SheetRead ( MyData , " c o n s t a n t ! B1" ) ;
Size of the problem 3 Numbjob from SheetRead ( MyData , " c o n s t a n t ! B2" ) ;
4 B e n e f i t from SheetRead ( MyData , " S h e e t 2 ! B2 : E5" ) ;
i : index of the worker
5 X t o S h e e t W r i t e ( MyData , " S h e e t 3 ! A1 : D4" ) ;
j : index of the job
Parameters File (*.xlsx)
Bij : the benefit if the worker i is assigned to job j
Decision variables
(b) sheet "Sheet2" of "assginment.xlsx" file
Xij : binary variable , Xij = 1 if the worker i is assigned to the job j otherwise Xij = 0.
(a) sheet "constant" of "assginment.xlsx" file A B C D E
Objective:
XX
A B 1 Worker/Job 1 2 3 4
max Z = Bij Xij
2 1 1 4 5 0
∀i ∀j 1 Numb of workers 4
3 2 3 2 1 4
Constrainst: 2 Numb of jobs 4
X 4 3 1 4 5 1
Xij = 1, ∀i 5 4 6 2 5 3
∀j
X Table 2: Data for Assignment Problem
Xij = 1, ∀j
∀i

Code for CPLEX 4 FAQs

File (*.mod)
1 i n t Numbworker = . . . ; 4.1 Question 1
2 i n t Numbjob = . . . ;
3 r a n g e Worker = 1 . . Numbworker ; Ask: How can 3 dimensions data be read from an Excel file?
4 r a n g e Job = 1 . . Numbjob ;
Answer: Firstly, create a temporary 1 or 2 dimensional data for reading data from excel file. Then convert
5 f l o a t B e n e f i t [ Worker ] [ Job ] = . . . ;
6 dvar b o o l e a n X [ Worker ] [ Job ] ;
them into the right form.
7 e x e c u t e PRE_PROCESSING { Example 1
8 c p l e x . epgap = 0 . 1 ; We need to read a 3 dimension data A[Level][M onth][P roduct] from an excel file. The name of the Excel
9 c p l e x . t i l i m = 100 ;
file is “MatrixConvert.xlsx”. This file includes 2 sheets.
10 }
11 maximize
12 sum ( i i n Worker , j i n Job ) X[ i ] [ j ] ∗ B e n e f i t [ i ] [ j ] ;
• The 1st sheet names “Scalar”.
13 s u b j e c t to {
• The 2nd sheet names “3DimData”.
14 constraint_1 :
15 f o r a l l ( i i n Worker ) {
16 sum ( j i n Job )X[ i ] [ j ] == 1 ; File (*.xlsx)
17 }

06 Guide for Cplex Usage Page 5 06 Guide for Cplex Usage Page 6
 Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

(b) sheet "3DimData" of “MatrixConvert.xlsx” file (a) sheet "Scalar" of “MatrixConvert.xlsx” file

A B C D E F G A B
(a) sheet "Scalar" of “MatrixConvert.xlsx” file
1 P1 P2 P3 P4 P5 1 Number of Quarters 2
A B 2 Month 1 1 2 4 5 5 2 Number of Months 2
Level 1
1 Number of Months 2 3 Month 2 2 3 5 4 4 3 Number of Levels 3
2 Number of Levels 3 4 Month 1 2 4 3 4 5 4 Number of Products 5
Level 2
3 Number of Products 5 5 Month 2 1 1 1 3 2 (b) sheet "4DimData" of “MatrixConvert.xlsx” file
6 Month 1 2 4 3 6 1
Level 3 A B C D E F G H
7 Month 2 2 3 5 7 2
1 P1 P2 P3 P4 P5
Table 3: Data for Reading 3 Dimensional Data Problem 2 Month 1 1 2 4 5 5
Level 1
3 Month 2 2 3 5 4 4
4 Month 1 2 4 3 4 5
File(*.mod) Quarter 1 Level 2
5 Month 2 1 1 1 3 2
6 Month 1 2 4 3 6 1
Level 3
7 Month 2 2 3 5 7 2
1 i n t numbMonth = . . . ;
8 Month 1 4 5 7 8 8
2 i n t numbProduct = . . . ; Level 1
9 Month 2 3 1 5 6 11
3 i n t numbLevel = . . . ;
10 Month 1 4 2 5 6 4
4 r a n g e Month = 1 . . numbMonth ; Quarter 2 Level 2
11 Month 2 6 4 13 8 9
5 r a n g e Product = 1 . . numbProduct ;
12 Month 1 11 2 11 3 8
6 r a n g e L e v e l = 1 . . numbLevel ; Level 3
13 Month 2 2 3 5 9 7
7 r a n g e TempRange = 1 . . numbLevel ∗ numbMonth ; / / c r e a t e
8 f l o a t TempMatrix [ TempRange ] [ Product ] = . . . ;
Table 4: Data for Reading 4 Dimensional Data
9 f l o a t A[ i i n L e v e l ] [ j i n Month ] [ l i n Product ] = TempMatrix [ ( i − 1 ) ∗ numbMonth + j ] [ l ] ;

File(*.dat) 6 r a n g e Product = 1 . . numbProduct ;

7 r a n g e L e v e l = 1 . . numbLevel ;
8 r a n g e Qua rt er = 1 . . numbQuarter ;
1 S h e e t C o n n e c t i o n Data ( " MatrixConvert . x l s x " ) ; 9 r a n g e TempRange = 1 . . numbQuarter ∗ numbLevel ∗ numbMonth ; / / c r e a t e temporary m a t r i x
2 numbMonth from SheetRead ( Data , " S c a l a r ! B1" ) ; 10 f l o a t TempMatrix [ TempRange ] [ Product ] = . . . ;
3 numbLevel from SheetRead ( Data , " S c a l a r ! B2" ) ; 11 f l o a t A[ h i n Quar t e r ] [ i i n L e v e l ] [ j i n Month ] [ l i n Product ] = TempMatrix [ ( h − 1 ) ∗
4 numbProduct from SheetRead ( Data , " S c a l a r ! B3" ) ; numbLevel ∗ numbMonth + ( i − 1 ) ∗ numbMonth + j ] [ l ] ;
5 TempMatrix from SheetRead ( Data , " 3DimData ! C2 : G7" ) ;

File(*.dat)

4.2 Question 2
1 S h e e t C o n n e c t i o n Data ( " MatrixConvert4D . x l s x " ) ;
Ask: How can 4 dimensions data be read from an Excel files? 2 numbQuarter from SheetRead ( Data , " S c a l a r ! B1" ) ;
3 numbLevel from SheetRead ( Data , " S c a l a r ! B2" ) ;
Answer: Firstly, create a temporary 1 or 2 dimensional data for reading data from excel file. Then convert
4 numbMonth from SheetRead ( Data , " S c a l a r ! B3" ) ;
them into the right form. 5 numbProduct from SheetRead ( Data , " S c a l a r ! B4" ) ;
Example 1 6 TempMatrix from SheetRead ( Data , " 4DimData ! D2 : H13" ) ;
We need to read a 4 dimension data A[Quarter][Level][M onth][P roduct] from an excel file. The name of
the Excel file is “MatrixConvert.xlsx”. This file includes 2 sheets.
4.3 Question 3
• The 1st sheet names “Scalar”.
Ask:How can we setup the constraints on the index with simple filter when using forall ?
• The 2nd sheet names “3DimData”. Answer: Using following format
1 f o r a l l ( i i n F a c i l i t y , j i n Product : filter )
File (*.xlsx)
File(*.mod) For example
1 f o r a l l ( i i n F a c i l i t y , j i n Product : i <j )
2 f o r a l l ( i i n F a c i l i t y , j i n Product : Param [ i ] [ j ]==0)
1 i n t numbMonth = ...;
3 f o r a l l ( i i n F a c i l i t y , j i n Product : Param [ i ] [ j ]>=0)
2 i n t numbProduct = ...;
3 i n t numbLevel = ...;
4 i n t numbQuarter = ...; Note: when the filter condition becomes complex, it is required if-then command
5 r a n g e Month = 1 . . numbMonth ;

06 Guide for Cplex Usage Page 7 06 Guide for Cplex Usage Page 8
 Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

1 f o r a l l ( i in job ) { 11 i f ( sum ( j i n j o b ) Pre [ i ] [ j ]==0) {

2 i f ( ( sum j i n j o b ) Param [ i ] [ j ]==0) { 12 Fin [ i ]==Pro [ i ] ;
3 Fin [ i ]==Pro [ i ] ; 13 }
4 } 14 }
5 }

4.5 Question 5
4.4 Question 4
Ask: How can we express the implication condition in CPLEX ?
Ask: How can we express the prerequisite constraints or set constraints in CPLEX Answer: Using =>.
Answer: Convert the constraints into the matrix form For example
Example Let x[i] be the binary variable, x[i] = 1 if the facility i is open, otherwise; x[i] = 0
Given the project includes 5 activities as figure above. Each activity will have its processing time. Find the Let Q[i][j] be the quantity of product j manufactured in facility i.
The relationship among these factors can be expressed as:
2

1 i f x [ i ]=0 then Q[ i ] [ j ]=0

This IF-THEN can be expressed as

1 3 5
X
Q[i][j] ≤ BigM × x[i], ∀i
∀j

4 In CPLEX it can be expressed as

1 f o r a l l ( i i n F a c i l i t y , j i n Product ) {
Figure 1: The Graph of Question 4 2 X[ i ]==0 => Q[ i ] [ j ] == 0 ;
3 }
minimum completion time of the project.
In using the implication, the left side of => must be the variable, i.e., X[i].

The most difficult part of this problem is to deal with the order of the activities. To handle this, transform 4.6 Question 6
the graph into the prerequisite matrix.
Ask: Solve the transportation problem of the supply chain below: Answer:
(a) Prerequisite Matrix

J/J 1 2 3 4 5
(b) Processing Time Matrix
1 0 0 0 0 0
2 0 0 0 0 0 Action 1 2 3 4 5 1 1 1
3 1 0 0 0 0 Processing Time 3 2 4 5 8
4 1 0 0 0 0
5 0 1 1 1 0
2 2 2
Table 5: Data for Question 4

In the prerequisit matrix P re(i, j) = 1 means that activity j must be finished right before activity i.
File(*.mod)
3 3 3
Manufacturers Distributors Retailers
1 i n t numbJob = . . . ;
2 r a n g e j o b = 1 . . numbJob ;
3 i n t Pro [ j o b ] = . . . ;
Figure 2: The Supply Chain Model of Question 6
4 i n t Pre [ j o b ] [ j o b ] = . . . ;
5 dvar f l o a t+ Fin [ j o b ] ; Parameters
6 m i n i m i z e sum ( i i n j o b ) Fin [ i ] ; M Dij : unit transportation cost between manufacturer i and distributor j
7 s u b j e c t to {
CapM Dij : capacity of arc between manufacturer i and distributors j
8 f o r a l l ( i i n job , j i n j o b : Pre [ i ] [ j ]==1)
9 Fin [ i ]>=Fin [ j ]+ Pro [ i ] ; DRjk : unit transportation cost between distributors j and retailers k
10 f o r a l l ( i in job ) CapDRj k: capacity of arc between distributors j and retailers k

06 Guide for Cplex Usage Page 9 06 Guide for Cplex Usage Page 10
Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

Fj : fixed cost for opening distribution center j 1 i n t numbManu = . . . ;

Mi : the product quantity of manufacturer i 2 i n t numbDist = . . . ;
3 i n t numbRetl = . . . ;
Rk : the demand of retailer k
4 r a n g e Manu= 1 . .numbManu ;
It is assumed that 5 r a n g e D i s t = 1 . . numbDist ;
X X
Mi = Rk 6 r a n g e R e t l = 1 . . numbRetl ;
∀i ∀k 7 f l o a t MD[ Manu ] [ D i s t ] = . . . ;
8 f l o a t CapMD[ Manu ] [ D i s t ] = . . . ;
Variables
9 f l o a t DR[ D i s t ] [ R e t l ] = . . . ;
Xij = quantity of product from manufacturer i and distribution center j 10 f l o a t CapDR [ D i s t ] [ R e t l ] = . . . ;
Yjk = quantity of product from distribution center j and retailer k 11 f l o a t F[ Dist ] = . . . ;
Zj = 1 if the distribution center j is open, Zj = 0 otherwise 12 f l o a t M[ Manu ] = . . . ;
13 f l o a t R[ R e t l ] = . . . ;
14 dvar f l o a t+ X[ Manu ] [ D i s t ] ;
15 dvar f l o a t+ Y[ D i s t ] [ R e t l ] ;
dvar b o o l e a n Z [ D i s t ] ;
X XX XX 16
max Z = Z j Fj + Xij M Dij + Yjk DRjk
17 minimize (
j i j j k
18 sum ( j i n D i s t )F [ j ] ∗ Z [ j ]+sum ( i i n Manu , j i n D i s t )X[ i ] [ j ] ∗MD[ i ] [ j ]+sum ( j i n D i s t , k i n R e t l )Y[
subject to: j ] [ k ] ∗DR[ j ] [ k ] ) ;
19 s u b j e c t to {
X 20 f o r a l l ( i i n Manu)
Xij = Mi , ∀i
21 sum ( j i n D i s t )X[ i ] [ j ]==M[ i ] ;
j
22
X X
Xij = Yjk , ∀j 23 f o r a l l (k in Retl )
i k 24 sum ( j i n D i s t )Y[ j ] [ k]==R[ k ] ;
X 25

Yjk = Rk , ∀i 26 f o r a l l ( i i n Manu , j i n D i s t )
j 27 X[ i ] [ j ]<=CapMD[ i ] [ j ] ;
28
Xij ≤ M Dij , ∀i, ∀j
29 f o r a l l ( j in Dist , k in Retl )
30 Y[ j ] [ k]<=CapDR [ j ] [ k ] ;
Yjk ≤ DRjk , ∀j, ∀k
31
X
Yjk ≤ BigM × Zj , ∀j 32 f o r a l l ( j in Dist )
k 33 sum ( i i n Manu)X[ i ] [ j ]==sum ( k i n R e t l )Y[ j ] [ k ] ;
34
The data for this problem are given by: 35 f o r a l l ( j i n D i s t , i i n Manu)
36 Z [ j ]==0=>X[ i ] [ j ]==0;
37 }
(a) MD Arc Capacity (b) DK Arc Capacity

CapM D 1 2 3 CapDK 1 2 3 File ( *.dat)

1 10000 10000 300 1 10000 10000 1000
2 10000 10000 400 2 10000 10000 1000
1 S h e e t C o n n e c t i o n MyData ( " SupplyData . x l s x " ) ;
3 10000 10000 200 3 500 600 200
2 numbManu=3;
(c) One Dimensional Data (d) DR Distrance Matrix
3 numbDist =3;
Mi Fj Rk DR 1 2 3 4 numbRetl =3;
300 3000 250 1 20 17 15 5 MD from SheetRead ( MyData , " S h e e t 2 ! B7 : D9" ) ;
400 4000 250 2 13 22 20 6 CapMD from SheetRead ( MyData , " S h e e t 2 ! B2 : D4" ) ;
500 100000 700 3 1 2 1 7 DR from SheetRead ( MyData , " S h e e t 2 ! G7 : I 9 " ) ;
(e) MD Distance Matrix 8 CapDR from SheetRead ( MyData , " S h e e t 2 ! G2 : I 4 " ) ;
9 F from SheetRead ( MyData , " S h e e t 1 ! B2 : B4" ) ;
MD 1 2 3 10 M from SheetRead ( MyData , " S h e e t 1 ! A2 : A4" ) ;
1 20 17 1 11 R from SheetRead ( MyData , " S h e e t 1 ! C2 : C4" ) ;
2 13 22 1
3 15 19 1
4.7 Question 7
Table 6: Parameters of Transportation Problem
Ask: Find the shortest path from node 1 to node 5
File (*.mod) The prerequisite matrix:

06 Guide for Cplex Usage Page 11 06 Guide for Cplex Usage Page 12
 Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

5 D i s t from SheetRead ( Data , " S h e e t 2 ! B2 : F6" ) ;

2
4 8 4.8 Question 8
1 5

2
5 1 Problem: Sometime the result should be interpreted into the readable form. For example, in the case of VRP
3 4 4 problem, the solutions are often expressed as X[i][j] = 0 or 1. It is often that only values with X[i][j] = 1
are concerned. These values can be written to a text file as follows:
1 e x e c u t e WRITE_RESULT{
Figure 3: The Network of Question 7 2 v a r o f i l e = new I l o O p l O u t p u t F i l e ( " R e s u l t . t x t " ) ;
3 o f i l e . w r i t e l n ( c p l e x . getObjValue ( ) ) ; / / Get t h e v a l u e o f t h e o b j e c t i v e f u n c t i o n
(a) The prerequisite matrix (b) The post matrix 4 f o r ( i in rangeI ) f o r ( j in rangeJ ) {
P re 1 2 3 4 5 P ost 1 2 3 4 5 5 i f (X[ i ] [ j ]==1) {
6 o f i l e . w r i t e l n ( "The v a l u e o f X[ " , i , " ] [ " , j , " ] : " ,X[ i ] [ j ] ) ;
1 0 0 0 0 0 1 0 1 1 0 0
7 }
2 1 0 0 0 0 2 0 0 0 1 1
8 }
3 1 0 0 0 0 3 0 0 0 1 0
9 o f i l e . w r i t e l n ( "−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−" ) ;
4 0 1 1 0 0 4 0 0 1 0 1
10 o f i l e . close () ;
5 0 1 0 1 0 5 0 0 0 0 0
11 }
(c) The distance matrix

Distance 1 2 3 4 5
1 1000 1000 1000 1000 1000 5 How to use tuple
2 1000 1000 1000 2 8
3 1000 1000 1000 4 1000
4 1000 1000 1000 1000 1 Problem: Sometimes it is exhausted to enumerate all data, due to the data size. In this case, it is better
5 1000 1000 1000 1000 1000 to use tuple approach to handle this. The tuple can be understood as a user-defined type. The tuple is
especially useful when the data are spare.
Table 7: Distance Matrix Network
Step in using tuple:

File(*.mod) • Define the tuple

• Create the sets corresponding to tuple

1 i n t numbNode = . . . ;
2 r a n g e Node = 1 . . numbNode ;
• Read parameters which use tuple as index
3 f l o a t D i s t [ Node ] [ Node ] = . . . ;
4 f l o a t Pre [ Node ] [ Node ] = . . . ;
• Declare the decision variables which use tuple as index
5 f l o a t Post [ Node ] [ Node ] = . . . ;
6 dvar b o o l e a n x [ Node ] [ Node ] ;
7 m i n i m i z e sum ( i i n Node , j i n Node ) x [ i ] [ j ] ∗ D i s t [ i ] [ j ] ; Example:
8 s u b j e c t to { Consider the Assignment problem when the size is 10 jobs, 10 workers. In this problem, it is often that given
9 sum ( i i n 2 . . numbNode−1)x [ 1 ] [ i ]==1; a specific worker, he can be only in charge of some jobs. Other jobs are unsuitable for him.
10 sum ( i i n 2 . . numbNode−1)x [ i ] [ numbNode]==1;
In traditional approach, the benefit of these jobs will be assigned to −∞ in the benefit matrix. If we declare
11

12 f o r a l l ( i i n 2 . . numbNode−1){ a full benefit matrix, it requires 10 × 10 entries and the same number of variables. The situation is even
13 sum ( j i n Node ) x [ j ] [ i ]==sum ( j i n Node ) x [ i ] [ j ] ; worse when the size of the problem increase to 1000 × 1000, it requires 106 entries.
14 }
To reduce the efforts for data input as well as the number of variables, tuple is used.
15

16 f o r a l l ( i i n Node , j i n Node ) {
The framework for using tuple is given as follows:
17 x [ j ] [ i ]<=Pre [ i ] [ j ] ;
18 x [ i ] [ j ]<=Post [ i ] [ j ] ;
1 t u p l e tupleName {
19 }
2 dataType a t t r i b u t e 1 ;
20 }
3 dataType a t t r i b u t e 2 ;
4 };
File(*.dat) 5 s e t o f ( tupleName ) t u p l e S e t = . . . ;
1 S h e e t C o n n e c t i o n Data ( " S h o r t e s t P a t h . x l s x " ) ; 6 f l o a t ParameterA [ t u p l e S e t ] = . . . ;
2 numbNode=5; 7 dvar b o o l e a n X[ t u p l e S e t ] ;
3 Pre from SheetRead ( Data , " S h e e t 1 ! B2 : F6" ) ;
4 Post from SheetRead ( Data , " S h e e t 1 ! B10 : F14" ) ;

06 Guide for Cplex Usage Page 13 06 Guide for Cplex Usage Page 14
 Ho Chi Minh City International University Deterministic Models in Operation Research Ho Chi Minh City International University Deterministic Models in Operation Research
Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc Industrial Systems Engineering Department Lecturer: Phan Nguyen Ky Phuc

The tuple set includes all tuples that will be used as index . Consider the Assignment Problem with Benefit 35 }
Data given as tuple: 36 o f i l e . w r i t e l n ( "−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−" ) ;
37 o f i l e . close () ;
38 }
(b) The data in "Sheet2.xlsx"
File(*.dat)
A B C
1 Worker Job Benefit 1 S h e e t C o n n e c t i o n TupleData ( " AssignmentTuple . x l s x " ) ;
2 1 1 4 2 numJob from SheetRead ( TupleData , " S h e e t 1 ! B1" ) ;
3 1 2 3 3 numWorker from SheetRead ( TupleData , " S h e e t 1 ! B2" ) ;
(a) The data in "Sheet1.xlsx" 4 1 3 7 4 WorkJobSet from SheetRead ( TupleData , " S h e e t 2 ! A2 : B13" ) ;
5 1 4 5 5 B e n e f i t from SheetRead ( TupleData , " S h e e t 2 ! C2 : C13" ) ;
A B
6 2 2 7
1 numJob 5
7 2 3 5
2 numWorker 5
8 3 3 8 5.1 Define set through other sets
9 3 4 6
10 4 4 7 1 t u p l e Node{
11 4 5 9 2 i n t Machine ;
12 5 4 7 3 i n t Job ;
13 5 5 9 4 };
5 t u p l e Arc {
Table 8: Assignment Data 6 i n t Machine ;
7 i n t preJob ;
8 i n t sucJob
File(*.mod) 9 };
1 i n t numJob = . . . ; 10 {Node} NodeSet = . . . ;
2 i n t numWorker = . . . ; 11 { Arc } ArcSet={<m, j , k>|<m, j > i n NodeSet , <m, k> i n NodeSet : j !=k}
3 t u p l e WorkJob{
4 i n t Worker ;
5 i n t Job ;
6 }
7 r a n g e Job = 1 . . numJob ;
8 r a n g e Worker = 1 . . numWorker ;
9 s e t o f ( WorkJob ) WorkJobSet = . . . ;
10 f l o a t B e n e f i t [ WorkJobSet ] = . . . ;
11 dvar b o o l e a n X[ WorkJobSet ] ;
12

13 e x e c u t e PRE_PROCESSING {
14 c p l e x . epgap = 0 . 0 0 1 ;
15 c p l e x . t i l i m = 60∗60 ;
16 }
17 maximize sum(<w, j >i n WorkJobSet ) B e n e f i t [<w, j >]∗X[<w, j > ] ;
18 s u b j e c t to {
19 constraint_1 :
20 f o r a l l ( j i n Job ) {
21 sum(<w, j > i n WorkJobSet )X[<w, j >]==1;
22 }
23 constraint_2 :
24 f o r a l l (w i n Worker ) {
25 sum(<w, j > i n WorkJobSet )X[<w, j >]==1;
26 }
27 }
28 e x e c u t e WRITE_RESULT{
29 v a r o f i l e = new I l o O p l O u t p u t F i l e ( " R e s u l t . t x t " ) ;
30 o f i l e . w r i t e l n ( "The o b j e c t i v e f u n c t i o n v a l u e : " , c p l e x . getObjValue ( ) ) ; / / Get t h e v a l u e o f
the o b j e c t i v e f u n c t i o n
31 f o r ( v a r r i n WorkJobSet ) {
32 i f (X[ r ]==1) {
33 o f i l e . w r i t e l n ( " Worker [ " , r . Worker , " ] w i l l be a s s i g n e d t o Job [ " , r . Job , " ] " ) ;
34 }

06 Guide for Cplex Usage Page 15 06 Guide for Cplex Usage Page 16