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

[Noli Sicad]

Hello Xypron

Here is the initial proposal for UNPIVOT and PIVOT Syntax for Table IN
and Table OUT in MathProg.

~~~~~~~~~~

set CROPTYPE;

set SPECIES;

set ORIGIN;

set PRODUCT;

set DESTINATION;



param Yield{PRODUCT,CROPTYPE,AGECLASS}, default 0;



CREATE TABLE tblProduct

 (

        ProductID                       char (1),

        Product                 varchar (100),

        Type                    varchar (100),

        Status                  varchar (100),

)



table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'

 'SELECT * FROM  tblProduct' :

PRODUCT <- [Product], Type, Status;

display PRODUCT;





## Pivot table

~~~~~~~~~~~~~~~~~~~~~~~

# This is AMPL syntax  - Pivot table

printf "Reading Yields....\n";

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

read table tblData;

~~~~~~~~~~~~~~~~~~~~~~~~



#Propose MathProg - Pivot table

table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'

 'SELECT * FROM  tblProduct' :

 Yield <-[Croptype, Age, {p in PRODUCT: Type[p] <> 'Residue'}], Yield];

Display Yield





# Unpivot table



~~~~~~~~~~~

# This is AMPL syntax  - Pivot table in Table OUT

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;

~~~~~~~~~~~~



# Propose syntax  - Pivot table in Table OUT

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.


These are all tentative. It is up to you how you can make this working.

Anybody got some ideas how to improve these syntax?

Thanks a lot.