FICO Xpress Optimization Examples Repository: Reading 3-dimensional arrays

FICO Xpress Optimization Examples Repository

Reading 3-dimensional arrays

Description

This example shows how to read a 3-dimensional array from an excel file (threedimarr.mos) and from a generic spreadsheet (threedimarr5.mos) where the last index is specifed 'across the columns', resulting in a pivot-style table format where the first N-1 columns contain index values (here: 2 index columns). Model versions (2) and (4) show the same example for databases, and model version (6) works with data in CSV format.

Note that standard use of the 'partndx' option for spreadsheets assumes that the last index is a set of type 'range', the example partidxstr.mos defines a utility routine 'makelabel' that makes it possible to work with a 'set of string' for the last index.

threedimarr.zip

[download all files]

Source Files

By clicking on a file name, a preview is opened at the bottom of this page.

threedimarr.mos	[download]
threedimarr2.mos	[download]
threedimarr3.mos	[download]
threedimarr4.mos	[download]
threedimarr5.mos	[download]
threedimarr6.mos	[download]
partidxstr.mos	[download]

Data Files

threedim.csv	[download]
threedim.xls	[download]
threedim.xlsx	[download]
threedim.mdb	[download]
threedim.sqlite	[download]
partidxdata.csv	[download]
partidxdata.xls	[download]
partidxdata.xlsx	[download]

threedimarr2.mos

(!*******************************************************
   Mosel Example Problems 
   ======================

   file threedimarr2.mos
   `````````````````````
   Reading 3-dimensional arrays.
   - Using SQL commands -
       
   (c) 2014 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2014, rev. Aug. 2023
*******************************************************!)

model "ThreeDimArr (SQL)"
 uses "mmodbc", "mmsystem"

 parameters
!  CSTR = "threedim.xls"      ! Use Excel spreadsheet `threedim.xls'
  CSTR = "threedim.mdb"       ! Use Access database `threedim.mdb'
!  CSTR = 'threedim.sqlite'   ! Use SQLite database `threedim.sqlite' directly
                              ! Use SQLite database `threedim.sqlite' via ODBC
!  CSTR = 'DSN=sqlite;DATABASE=threedim.sqlite'
 end-parameters

 declarations
  I: range
  J: set of string
  K = 1..5	              ! The last index set must be defined in the model
  A: dynamic array(I,J,K) of real
  Idx3: text
 end-declarations

 SQLconnect(CSTR)
 if not getparam("SQLsuccess"): setioerr("Database connection failed")

 setparam("SQLndxcol", false) ! Partially indexed data
 
 forall(k in K) Idx3+= (", Index3_"+k )
 SQLexecute("select Index1, Index2" + Idx3 + " from Tab_23", A)
 SQLdisconnect

 writeln("A: ")
 forall(i in I, j in J, k in K | exists(A(i,j,k)))
  writeln("A(", i, ",", j, ",", k, "): ", A(i,j,k))

  
end-model