Re: [Help-glpk] Problems with Connecting GLPK to Excel

[Aly Megahed]

Hi Xypron,

As I mentioned in my previous message, I was finally able to solve most 
problems of connecting GLPK to Access. The only problem left that I really need 
your help on in that concern is the one below. So, I'd really appreciate your 
input on it!

Aly


----- Original Message -----
From: "Aly Megahed" <address@hidden>
To: "Xypron" <address@hidden>
Sent: Saturday, August 28, 2010 9:52:51 AM GMT -05:00 US/Canada Eastern
Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel

Hello Xypron,

Man, I really feel bad keeping to ask you so many times, but I really really 
appreciate all your help. Without that help, there was no way for me to begin 
getting the results I'm getting now!

So, with regards to your comments below, unfortunately, I am still not able to 
output records that did not exist before with their corresponding variable 
values. Let me describe the problem again for your convenience:

I have a variable called bq, indexed on the sets C, P, T, T (usually the 
indices are k, p, t, u (i.e. both t and u belong to the set T) for these sets, 
respectively). That variable is to be outputted to the field "Quantity" in the 
table 'CustomerBackorderPeriodPeriodProducts', while there are four other 
fields referring to the four indices. These are "FacilityID", "PeriodID", 
"BackorderPeriodID" and "PeriodID" respectively.

Using an OUT table and the UPDATE statement, I can output the values of bq that 
have their corresponding indices existing in the table. However, I am still not 
able to use the INSERT statement in an OUT table to correctly output the 
variable values that correspond to indices that are not there in the table, and 
also output these indices (so for those variable values, complete records 
should be pasted in the table, not only the variable value).

Believe me I tried SO many trials using different combinations of what you 
mentioned before, but with no luck. Asking experts in SQL didn't help as well, 
as the problem is very closely related to optimization in the sense of 
indices/parameters/...etc. So, do you mind telling me what the code should look 
like for this?

Your help would be greatly appreciated.

Aly

----- Original Message -----
From: "Xypron" <address@hidden>
To: address@hidden
Cc: "help-glpk" <address@hidden>
Sent: Tuesday, August 24, 2010 5:44:31 PM GMT -05:00 US/Canada Eastern
Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel

Hello Aly,

the key columns in your table
CustomerBackorderPeriodPeriodProducts are:
- FacilityID
- PeriodID
- BackorderPeriodID
- ProductID

Hence to find the set of entries that already exist it is sufficient to 
SELECT these columns only. No need to read UnitCost.

 > set bc_existent{ k in C, p in P, t in T} := setof{(FacilityID, 
ProductID, PeriodID, BackorderPeriodID, UnitCost) in bc_dummy_set_2} 
BackorderPeriodID;
This will create sets. Each of the sets contains all BackorderPeriodIDs 
because the right hand side of the assignment does not depend on [k, p, t].

If you only want those BackorderPeriodIDs that relate to [k,p,t] you 
have to write:

set bc_existent{ k in C, p in P, t in T} := setof{(k, p, t, 
BackorderPeriodID, UnitCost) in bc_dummy_set_2} BackorderPeriodID;

 > I got a syntax error of "u not defined" whenever I tried to make it as
 > table bq_insert { k in C, p in P, t in T, u in T diff   
bc_existent[p, t, u]  } OUT

{ k in C, p in P, t in T, u in T diff   bc_existent[p, t, u]  }
is interpreted as :

for each k in C {
   for each p in P {
     for each t in T {
       for each u in ( T diff bc_existent[p, t, u] ) {
       }
     }
   }
}

You define u to be the index variable while iterating over (T diff 
bc_existent[p, t, u]). This is impossible. The set to iterate over has 
to be calculated before the iteration can take place.

Best regards

Xypron



address@hidden wrote:
> Hello Xypron,
>
> I have been trying to do the "Insert" table as the one you described below, 
> but it doesn't want to work at all!!
>
> I have the following:
>
>
>
> set bc_dummy_set_2, dimen 5;
> table bc_kptu_dummy_2 IN "ODBC"
> 'FileDSN=.\d2.dsn;READONLY=FALSE'
> 'CustomerBackorderPeriodPeriodProducts':
> bc_dummy_set_2<-[FacilityID, ProductID, PeriodID, BackorderPeriodID, 
> UnitCost];
> set bc_existent{ k in C, p in P, t in T} := setof{(FacilityID, ProductID, 
> PeriodID, BackorderPeriodID, UnitCost) in bc_dummy_set_2} BackorderPeriodID; 
> /*line 6 */
>
> table bq_insert { k in C, p in P, t in T, u in T diff   bc_existent[k,p, t]  
> } OUT
> 'ODBC'
> 'FileDSN=.\d2.dsn'
> 'INSERT INTO CustomerBAckorderPeriodPeriodProducts (k,p,t,u,UnitCost,bq, 
> TotalCost, VariableID)'
> 'VALUES (?,?,?,?,0,?,0,0);':
> k,p,t,u,bq[k,p,t,u];
>
>
>
> Note that that table CustomerBackorderPeriodPeriodProducts has 7 fields, 
> which are the following:
>
> a) 4 indices (defined on customers, products, periods, periods), the field 
> names are FacilityID, ProductID, PeriodID and BackorderPeriodID respectively,
> b) The parameter UnitCost,
> c) The variable bq, which has the field name Quantity,
> d) 2 other fields (that are not used in this model), having the names 
> TotalCost and VariableID.
>
>
> And I'm here trying to let it insert the non-existent records by inserting 
> values for each of the 4 indices and inserting the corresponding variable 
> value, and putting values of each of the parameter UnitCost in addition to 
> the two fields TotalCost and VariableID as zeros.
>
> Also, please note that in "line 6" above in my set bc_existent, I wanted it 
> to contain all combinations of FacilityID, ProductID, PeriodID and 
> BackorderPeriodID that have corresponding parameter bc value, but I had to 
> define it on only 3 of the parameters and put the fourth at the end of the 
> statement to not get a syntex error. Also, I got a syntax error of "u not 
> defined" whenever I tried to make it as (and I don't know why):
>
> set bc_existent{ p in P, t in T, u in T} := setof{(FacilityID, ProductID, 
> PeriodID, BackorderPeriodID, UnitCost) in bc_dummy_set_2} FacilityID;
> table bq_insert { k in C, p in P, t in T, u in T diff   bc_existent[p, t, u]  
> } OUT
>
>
> I'd appreciate your input a lot,
>
> Thanks a lot!!!
>
> Aly
>
> ----- Original Message -----
> From: "glpk xypron"<address@hidden>
> To: address@hidden
> Cc: address@hidden
> Sent: Thursday, July 29, 2010 2:03:19 PM GMT -05:00 US/Canada Eastern
> Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel
>
> Hello Aly,
>
> you will need two table OUT statements, one for updating and
> one for inserting (see example below).
>
> Some none standard extensions like MySQL REPLACE can do both.
>
> Best regards
>
> Xypron
>
>
> # products
> set P;
> # periods
> set T;
> # demand (product, period, quantity)
> set D, dimen 3;
> # periods with demand
> set X{p in P} := setof{(p, t, d) in D} t;
> # stock (we will use some dummy value in this example)
> param s{P,T} := Uniform01();
> solve;
> # create table for our test
> table CRE {(p,t,d) in D} OUT
>    'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
>    'DROP TABLE IF EXISTS tbl;' # not supported by Access
>    'CREATE TABLE tbl ('
>    '  p TEXT(10),'
>    '  t INT,'
>    '  d FLOAT,'
>    '  s FLOAT,'
>    '  PRIMARY KEY (p(10), t) );'
>    'INSERT INTO tbl (p, t, d, s)'
>    '  VALUES (?, ?, ?, 0);' :
>    p, t, d;
> # update only existing records
> table UPD { (p, t, d) in D } OUT
>    'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
>    'UPDATE tbl'
>    '  SET s = ?'
>    '  WHERE p = ? AND t = ?' :
>    s[p,t], p, t;
> # insert missing records
> table INS {p in P, t in T diff X[p]} OUT
>    'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
>    'INSERT INTO tbl (p, t, d, s)'
>    '  VALUES (?, ?, 0, ?);' :
>    p, t, s[p,t];
>
> data;
>
> set P := p1 p2 p3;
> set T := 1 2 3 4 5;
> set D :=
>    p1 2 43
>    p1 4 16
>    p2 5 13
>    p3 1  7
>    p3 4 18;
> end;
>
>
> -------- Original-Nachricht --------
>    
>> Datum: Thu, 29 Jul 2010 04:33:50 -0400 (EDT)
>> Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel
>>      
>    
>> Hello Xypron,
>>
>> Well, it works PERFETLY!!! Thanks a lot for that!
>>
>> The only problem now in the same context happens when writing values back
>> for some of the non-existent records. Those non-existent records are
>> non-existent because the corresponding parameters are zero (and I don't input
>> records that have corresponding zero parameter values). However, the
>> corresponding variables for some of those records could be non-zero. An 
>> example is
>> the delivery quantity in a certain period. It could be non-zero even though
>> the demand for that period is zero. That is because such delivery quantity
>> will be stored to satisfy the demand in later periods for instance.
>>
>> So, there is no record corresponding to that period with zero demand in
>> the table before solving the model (note that the table I'm dealing with here
>> has a field for the index "periods", another for the parameter "demand"
>> and a third for the variable "delivered quantity") and so, an error happens
>> when the solver tries  to update the variable value for that period as it
>> doesn’t find its corresponding record.
>>
>> I hope it is clear!
>>
>>
>> Any ideas on the best way to deal with that?
>>
>> Best,
>>
>> Aly
>>
>> ----- Original Message -----
>> From: "glpk xypron"<address@hidden>
>> To: address@hidden, "xypron glpk"<address@hidden>
>> Cc: address@hidden
>> Sent: Tuesday, July 27, 2010 4:54:48 PM GMT -05:00 US/Canada Eastern
>> Subject: Re: [Help-glpk] Problems with Connecting GLPK to Excel
>>
>> Hello Aly,
>>
>> the model below shows the correct syntax.
>>
>> READONLY=FALSE is only needed for Excel not for Access.
>>
>> In the table OUT statement a domain specifies over which
>> entries you iterate.
>>
>> The last SQL statement is executed once per domain
>> entry.
>>
>> All question marks in this last SQL statement are replaced
>> by the variables given after the colon.
>>
>> Best regards
>>
>> Xypron
>>
>>
>>
>> set TF := { 'tf1', 'tf2', 'tf3' };
>> set P  := { 'p1', 'p2', 'p3'};
>> set T  := { 't1', 't2'};
>> param iq{TF, P, T} := Uniform01();
>>
>> solve;
>>
>> table t {j in TF, p in P, t in T} OUT 'ODBC'
>>    'FileDSN=.\d2.dsn'
>>    'UPDATE TransformerTypeStoragePeriodProducts'
>>    '  SET StorageQuantity = ?'
>>    '  WHERE TransformerTypeID = ?'
>>    '    AND PeriodID = ?'
>>    '    AND ProductID = ?' :
>>    iq[j, p, t], j, t, p;
>>
>> end;
>>
>>
>> -------- Original-Nachricht --------
>>      
>>> Datum: Tue, 27 Jul 2010 07:22:00 -0400 (EDT)
>>> Betreff: Re: [Help-glpk] Problems with Connecting GLPK to Excel
>>>        
>>      
>>> Hi Xypron,
>>>
>>> Well, I have really tried a lot, but still with no luck.
>>>
>>> The following SQL syntax works fine in Access:
>>>
>>> UPDATE TransformerTypeStoragePeriodProducts SET StorageQuantity = 5
>>> WHERE TransformerTypeID = 1
>>> AND PeriodID = 1
>>> AND ProductID = 1;
>>>
>>> Then, when I try to have this in GLPK:
>>>
>>> table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
>>>    'FileDSN=.\d2.dsn;READONLY=FALSE'
>>>
>>> 'UPDATE TransformerTypeStoragePeriodProducts'
>>> 'SET StorageQuantity = iq[j, p, t]'
>>>
>>> 'WHERE TransformerTypeID = j'
>>> 'AND PeriodID = t'
>>> 'AND ProductID = p';
>>>
>>> I get a syntax error saying that a semi colon is missing where expected.
>>>
>>> So, I tried this:
>>>
>>> table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
>>>    'FileDSN=.\d2.dsn;READONLY=FALSE'
>>>
>>> 'UPDATE TransformerTypeStoragePeriodProducts'
>>> 'SET StorageQuantity = iq[j, p, t]'
>>>
>>> 'WHERE TransformerTypeID = j'
>>> 'AND PeriodID = t'
>>> 'AND ProductID = p':
>>> j ~ TransformerTypeID, p ~ ProductID, t ~ PeriodID, iq[j, p, t] ~
>>> StorageQuantity;
>>>
>>> And that one (and other similar ones) fails to write and the driver
>>> reports the error 733: "error on writing data to table iq_jptyyy, model
>>> postsolving error". One of the other ones for instance that gave the
>>>        
>> same error and
>>      
>>> didn't work for instance is:
>>>
>>> table iq_jptyyy {j in TF, p in P, t in T} OUT 'ODBC'
>>>    'FileDSN=.\d2.dsn;READONLY=FALSE'
>>>
>>> 'UPDATE TransformerTypeStoragePeriodProducts'
>>> 'SET StorageQuantity = iq[j, p, t]':
>>> j ~ TransformerTypeID, p ~ ProductID, t ~ PeriodID, iq[j, p, t] ~
>>> StorageQuantity;
>>>
>>>
>>>
>>> Any ideas?
>>>
>>> Thanks a lot for the continuous support, patience and help!! I certainly
>>> appreciate it!!
>>>
>>> Best,
>>>
>>> Aly
>>>        
>> -- 
>> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
>> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>>
>> -- 
>> PhD Student
>>
>> Rm. 407 Main Building
>> H. Milton Stewart School of Industrial and Systems Engineering
>> Georgia Institute of Technology
>> 765 Ferst Dr., NW
>> Atlanta, Georgia 30332-0205, USA
>>
>>      
>    


-- 
PhD Student

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA


-- 
PhD Student

Rm. 407 Main Building
H. Milton Stewart School of Industrial and Systems Engineering
Georgia Institute of Technology
765 Ferst Dr., NW
Atlanta, Georgia 30332-0205, USA