![]() | |||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||
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.
Source Files By clicking on a file name, a preview is opened at the bottom of this page.
Data Files
partidxstr.mos (!****************************************************** Mosel Example Problems ====================== file partidxstr.mos ``````````````````` Reading partial-index table format with string column header for the last index for spreadsheet files in XLS, XLSX or CSV format The subroutine 'makelabel' provides a generic way of creating the required header definition for the 'partndx' format. (c) 2024 Fair Isaac Corporation author: S.Heipcke,Y.Colombani, Jan. 2024 *******************************************************!) model partidxstr uses "mmsystem", "mmreflect", "mmsheet" (!@doc. @descr Constructing the header selection string for reading a partially indexed array @param fname Spreadsheet file name (XLS, XLSX, or CSV format) @param rangename Name of a sheet or range (first sheet if not specified) @param u Array to be populated from the specified input @info The data array 'u' serves to determine the number of indices. If the array has N dimensions, it is assumed that the values of the first N-1 indices are contained in the N-1 initial columns of the data range and all other columns correspond to index values for the last dimension of the input array. !) public function makelabel(fname:text,rangename:string,u:any):string declarations colnames:dynamic array(1..1,1..1000) of string ! Due to the 'dynamic' flag this will work even if the actually selected area exceeds 1000 columns label:text allcols:set of string colorder:array(string) of integer cr: range end-declarations initialisations from fname colnames as "noindex;"+rangename end-initialisations if not u is array then writeln("Third argument of function 'makelabel' must be an array.") return end-if with nbdim=u.array.nbdim, ndx=u.array.index(nbdim).string do allcols:=union(i in nbdim..colnames.size){string(colnames(1,i))} if not allcols <= ndx then ndx+=allcols; end-if cr:=1..colnames.size forall(i in cr) colorder(colnames(1,i)):=i label:="skiph;partndx;"+rangename+"(#1" forall(c in 2..nbdim-1) label+=",#"+c forall(c in ndx|c in allcols) label+=",#"+colorder(c) end-do returned:=string(label+')') end-function !******************************************************************* procedure readpartindxl(drvfilename:string) declarations TabA: dynamic array(string,range,string) of real TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real it: iterator end-declarations !**** Reading a partially indexed table with string header fields fopen(drvfilename,F_INPUT) initialisations from getfname(F_INPUT) TabA as makelabel(getfname(F_INPUT),"[]",TabA) end-initialisations ! fclose(F_INPUT) writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3)) ! Enumerate the existing array entries (using an iterator since sets are not named) inititer(it,TabA) while (nextcell(it)) writeln(it.indices, ":", TabA(it)) !**** Reading a partially indexed table with preselection S1:={"xy","Z","BB","ab"} ! Prepopulating the index set ! fopen(drvfilename,F_INPUT) ! Various range specifications that result in the same selection: initialisations from getfname(F_INPUT) TabP as makelabel(getfname(F_INPUT),"pidxtest",TabP) ! predef. range name ! TabP as makelabel(getfname(F_INPUT),"[1$A:F]",TabP) ! TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP) end-initialisations fclose(F_INPUT) writeln("Index sets:", S1,R,S2) ! Enumerate the existing array entries forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k))) writeln([i,j,k], ":", TabP(i,j,k)) end-procedure !******************************************************************* procedure readpartindcsv declarations TabA: dynamic array(string,range,string) of real TabP: dynamic array(S1:set of string,R:range,S2:set of string) of real it: iterator end-declarations !**** Reading a partially indexed table with string header fields fopen("mmsheet.csv:partidxdata.csv",F_INPUT) initialisations from getfname(F_INPUT) TabA as makelabel(getfname(F_INPUT),"[]",TabA) end-initialisations ! fclose(F_INPUT) writeln("Index sets:", TabA.index(1), TabA.index(2), TabA.index(3)) ! Enumerate the existing array entries (using an iterator since sets are not named) inititer(it,TabA) while (nextcell(it)) writeln(it.indices, ":", TabA(it)) !**** Reading a partially indexed table with preselection S1:={"xy","Z","BB","ab"} ! Prepopulating the index set ! fopen("mmsheet.csv:partidxdata.csv",F_INPUT) initialisations from getfname(F_INPUT) TabP as makelabel(getfname(F_INPUT),"[A:F]",TabP) end-initialisations fclose(F_INPUT) writeln("Index sets:", S1,R,S2) ! Enumerate the existing array entries forall(i in S1, j in R, k in S2 | exists(TabP(i,j,k))) writeln([i,j,k], ":", TabP(i,j,k)) end-procedure !********************************************************** writeln("XLS:") readpartindxl("mmsheet.xls:partidxdata.xls") writeln("XLSX:") readpartindxl("mmsheet.xlsx:partidxdata.xlsx") writeln("CSV:") readpartindcsv end-model
| |||||||||||||||||||||||||||||||||||||||
© Copyright 2024 Fair Isaac Corporation. |