FICO Xpress Optimization Examples Repository
 FICO Optimization Community FICO Xpress Optimization Home

Auto-indexation for single-dimensional arrays

Description

This example shows how to read single-dimensional data tables from a spreadsheet or database file that only contains the data values. The data arrays must have an index set of type range that gets populated automatically based on the row count, either starting with the default value 1 or with a specified start value. Several arrays can be populated via a single statement.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Dense vs. sparse data format - Auto-indexation.

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

Data Files

autoindex.mos

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

file autoindex.mos

Auto-indexation feature for various methods of
data input from spreadsheets or databases.

(c) 2020 Fair Isaac Corporation
author: S. Heipcke, Apr. 2020
*******************************************************!)
model "autoindex"
uses "mmsheet", "mmetc", "mmodbc"

parameters
end-parameters

declarations
A,A2,B,C,B2,C2: dynamic array(range) of integer
end-declarations

initialisations from CSTR
A as 'autondx;[a:a]'                ! All data in column A
A2 as 'autondx=-3;[a:a]'            ! Use start value -3 for indexation
[B,C] as 'autondx=0;[a:b]'          ! Populating 2 arrays
[B2,C2] as 'autondx;[b:b](#1,#1)'   ~~COMMENT10~~
end-initialisations
writeln("SHT: A=", A)
writeln("SHT: A2=", A2)
writeln("SHT: B=", B, " C=", C)
writeln("SHT: B2=", B2, " C2=", C2)
end-procedure

! **** Reading from CSV-format files via 'diskdata' functionality ****
declarations
A,A2,B,C,B2,C2: dynamic array(range) of integer
end-declarations

initialisations from 'mmetc.diskdata:'
A as 'csv(1),autondx;adt.csv'      ! Selecting the first column
A2 as 'csv(1),autondx=-3;adt.csv'  ! Use start value -3 for indexation
[B,C] as 'csv,autondx;adt.csv'     ! Populating 2 arrays
end-initialisations
writeln("DD: A=", A)                 ! Output: [(1,10),(2,20),(3,30)]
writeln("DD: A2=", A2)
writeln("DD: B=", B, " C=", C)
writeln("DD: B2=", B2, " C2=", C2)
end-procedure

! **** Reading from SQLite database via SQL commands ****
declarations
A,A2,B,C,B2,C2: dynamic array(range) of integer
end-declarations

setparam("SQLautondx",true);
SQLexecute("select (Col1) from MyTable", A)  ! Selecting a single column
setparam("SQLfirstndx",-3)            ! Use start value -3 for indexation
SQLexecute("select (Col1) from MyTable", A2)
setparam("SQLfirstndx",0)
SQLexecute("select * from MyTable",[B,C])    ! Populating 2 arrays
SQLdisconnect
writeln("SQL: A=", A)
writeln("SQL: A2=", A2)
writeln("SQL: B=", B, " C=", C)
end-procedure

(!
! **** Creating a database via SQL commands ****
procedure createdb
declarations
A:array(integer) of integer
end-declarations

A::([10,20,30])[40,50,60]
SQLexecute("drop table if exists MyTable")
SQLexecute("create table MyTable (Col1 integer, Col2 integer)")
SQLexecute("insert into MyTable (Col1,Col2) values (?,?)", A)
SQLdisconnect
end-procedure
!)

! ******** Read input data from the different sources ********