Originally posted by: SystemAdmin

I've been looking to incorporate sparse model data to boost the memory usage and efficiency of my code (following the IBM whitepaper: ftp://ftp.software.ibm.com/common/ssi/sa/wh/n/wsw14059usen/WSW14059USEN.PDF). To this goal, I've looked at the OPL Example transp. In particular, I'll use transp2.mod as an example.

In transp2.mod two sets and a tuple are defined:

{string
} Cities = ...; 
{string
} Products = ...; tuple route 
{ string p; string o; string d; 
} 
{route
} Routes = ...;

The tuple set Routes is a subset of the full set (number_Products)*(number_Cities)*(number_Cities), so there is an advantage to later declare parameters and decision variables over the set Routes instead of over p in Productso in Citiesd in Cities.

The allowed set of routes is defined in transp2.dat as:

Routes = 
{ <bands GARY FRA>, <bands GARY DET>, ...

Is there a way to load this tuple set from Excel instead? What would that look like in an Excel spreadsheet? I suspect one column for each of the 3 entries <p,o,d> in Routes. Do I need to make an intermediate data structure that reads this matrix of string data from the Excel spreadsheet, and then pull the values one by one to form the elements of the tuple set +Routes+?

Thanks,

Alex
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

In your spreadsheet, you would have one column per field: p,o and d
and each row would represent an element of your tuple set.

In your OPL .dat file, you would have something like:

SheetConnection sheet("routes.xls");
Routes from SheetRead(sheet,"Sheet1!A1:C<numberOfElements>");

But that implies you know how many tuples you'll be reading.
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

Great! Thanks for the reply. You can use dynamic named ranges in Excel to avoid having to specify how many tuples are in the set within OPL-CPLEX Optimizer. I've attached an example Excel file to demonstrate.

Within OPL, the .mod file is unchanged but the .dat file would have:

SheetConnection sheet("SparseTuple.xlsx");
Routes from SheetRead(sheet,"Routes_name");

The Excel file SparseTuple.xlsx has a dynamic named range called Routes_name (see the Name Manager utility under Formulas). This range is defined as =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,3)
which means the cell range starting at Sheet1 cell A2 that has as many rows as (number of non-empty entries in column A minus 1) and 3 columns.
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Rajasekhar Kadambur

I am having a small doubt regarding reading 3d data i.e, same range of data from multiple Xl sheets

how to read 3d data in a single line from Xl file. thanks in advance

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: davidoff

I tried to describe one named range in a discontiguous way and I got the error

Exception from IBM ILOG Concert: Discontiguous Excel ranges are not supported.    

So I guess reading any data from several sheets in order to aggregate them in the same tuple collection is not authorized.

One workaround is to define as many collection as sheets and gather them . In the following lines, you could use either the same workbook with different ranges or even different workbooks

David

set1 from sheetRead(sheet1,range1);
set2 from sheetRead(sheet2,range2);
set3 from sheetRead(sheet3,range3);
...
myset =  set1 union set2 union set 3

#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Rajasekhar Kadambur

i am using same range of data for 100 different sets.

set1 from SheetRead(sheet,"sheet1!A1:J10")

set2 from SheetRead(sheet,"sheet2!A1:J10")

set3 from SheetRead(sheet,"sheet3!A1:J10")

.

.

.set100 from SheetRead(sheet,"sheet100!A1:J10")

 

For this data is there any smart way to call 100 sheets in single line for same range of data.

#DecisionOptimization
#OPLusingCPLEXOptimizer