[Help-glpk] Re: Inital Proposal for UNPIVOT and PIVOT Syntax for Table IN and Table OUT in MathProg

[glpk xypron]

Hello Noli,

thank you for the examples.

== PIVOTing ==

> table result{(t,i,j) in HARVEST: Y[t,i,j] > epsilon}  OUT "ODBC"
> 'Driver=SQLITE3;Database=Otago_p.sqlite;'
> 'DELETE FROM tblWoodflowSummary;'
> 'INSERT INTO tblWoodflowSummary VALUES (?,?,?,?,?,?,?)' :
> t ~ Period, sum {(t,i,j) in HARVEST} Y[t,i,j] ~ ClearfellArea,
>       {p in PRODUCT: Status[p] in YIELD} <sum {(t,i,j) in HARVEST}
> Y[t,i,j]*Yield[p,i,j] ~ (p)>;
> 
> I think the problem is 'INSERT INTO tblWoodflowSummary VALUES
> (?,?,?,?,?,?,?)'  since the algorithm wouldn't know how many values to
> be inserted.

You could think of only specifying a table name, e.g.
  table result{(t,i,j) in HARVEST: Y[t,i,j] > epsilon}  OUT "ODBC"
  'Driver=SQLITE3;Database=Otago_p.sqlite;'
  'DELETE FROM tblWoodflowSummary;'
  'tblWoodflowSummary' :
  t ~ Period, sum {(t,i,j) in HARVEST} Y[t,i,j] ~ ClearfellArea,
        {p in PRODUCT: Status[p] in YIELD} <sum {(t,i,j) in HARVEST}
  Y[t,i,j]*Yield[p,i,j] ~ (p)>;

To me pivoting only makes sense when the output shall be loaded
into a spreadsheet program like OpenOffice Calc or Excel.

In this case the current version of GLPK already allows to create
an appropriate CSV or MHTML (for import to Excel) file using the
printf instruction.

== UNPIVOTing ==
Unpivoting may be necessary to import data from a spreadsheet.

> table tblData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in
> PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>;

Given a CSV file with a unknown number of columns - one per product -
how would you fill PRODUCT?

Best regards

Xypron


-- 
GMX DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für nur 19,99 Euro/mtl.!*
http://portal.gmx.net/de/go/dsl