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

[Noli Sicad]

Hello Xypron,

> == PIVOTing ==

> 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.

Yes. With endless possibilities in post processing the output results,
creating more sensitive tables. Most of software packages e.g. R
package, Red-R, GnuPlot, etc use pivot data formats to create bar,
line graph, scatterplots, etc.

If you are dealing with multiperiod MIP and LP models as time/period
one of the parameters, you really bad need to create / output your
tables in pivot formats to interpret it properly. Multiperiod land
based LP models in agrculture and forestry, power and electricity
allocation models, production planning are few example of these
models.

> 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.

This solves the CSV for Excel and Open Office but  not the SQL tables.

> == 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?

I forget to include some of the assumption of the model.


param Status{PRODUCT} symbolic;      #  Revenue, Cost, Yield, Ignore

param Type{PRODUCT} symbolic;   #  Product (Final), Thinning
(Intermediate), Residue

This is where Type[p] is reading the values. PRODUCT is enumerated in
the tblProduct tables in Product column and Status[p] in Status
column.

Here are some example.

-------Pivot---------

table tblIntermediateWoodflow OUT "ODBC" "Otago.mdb" "tblIntermediateWoodflow":
   {t in PERIOD, i in CROPTYPE, j in AGECLASS, p in PRODUCT: Type[p]
in INTERMEDIATE and Yield[p,i,j] > epsilon
   and sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t}
Y[tn,i,jn] > epsilon}  -> [Period, Croptype, AgeClass, Product],
   sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t} Y[tn,i,jn]  ~ Area,
   Yield[p,i,j] ~ Quantity,
   sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t} Y[tn,i,jn] *
Yield[p,i,j] ~ Perpetual,
   if (isCurrent[t,j]) then sum {(tn,i,jn) in HARVEST: tn >= t+1 and
jn = j+tn-t} Y[tn,i,jn] * Yield[p,i,j] else 0 ~ Current,
   if (isFuture[t,j]) then sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn
= j+tn-t} Y[tn,i,jn] * Yield[p,i,j]  else 0 ~ Future;
write table tblIntermediateWoodflow;

-------Unpivot-------------

printf "Writing Woodflow Summary Table ....\n";
table tblWoodflowSummary OUT "ODBC" "Otago.mdb" "tblWoodflowSummary":
        {t in PERIOD} -> [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)>;
write table tblWoodflowSummary;

Thanks again.

Regards, Noli