Re: [Help-glpk] Beware of using Access and Excel tables with ODBC (SQL statement) in MathProg

[Noli Sicad]

This is now the new models (sqlite and excel). I think the same data.
But still have different results.

~~~~~~~~~~~~~~~~~~~~
Reading model section from transp_odbc_sqlite3.mod...
Reading data section from transp_odbc_sqlite3.mod...
111 lines were read
Reading plants...
Connected to SQLite 3.6.22 -
C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3

I:
   Seattle
   'San Diego'
J:
   'New York'
   Chicago
   Topeka

a[Seattle] = 350
a['San Diego'] = 600

b['New York'] = 325
b[Chicago] = 300
b[Topeka] = 275

d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8

d['San Diego','New York'] = 2.5
d['San Diego',Chicago] = 1.8
d['San Diego',Topeka] = 1.4

Model has been successfully generated

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

Excel
Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp
SELECT * FROM [transp_dist$]
Generating cost...
Generating supply...
Generating demand...
Display statement at line 85
I:
   'San Diego'
   Seattle
J:
   Chicago
   'New York'
   Topeka

a['San Diego'] = 600
a[Seattle] = 350

b[Chicago] = 300
b['New York'] = 325
b[Topeka] = 275

d[Seattle,'New York'] = 2.5
d[Seattle,Chicago] = 1.7
d[Seattle,Topeka] = 1.8

d['San Diego',Chicago] = 1.8
d['San Diego','New York'] = 2.5
d['San Diego',Topeka] = 1.4

Model has been successfully generated



On 2/12/10, Noli Sicad <address@hidden> wrote:
> Hi Andrew,
>
> Yes, you are right. The coefficient in the distance between San Diego
> - New York and San Diego Chichago were interchanged in Access and
> Excel. I hand coded the Access data early morning (1AM) and export to
> Excel the data the next day. While in sqlite I modified the SQL
> statement from mysql for sqlite and run the sql statements.
>
> I corrected the Access and Excel data. However, the Excel model still
> persistent to have the wrong solution. I rebooted my computer. I do
> again the ODBC setup for excel, to refresh the link. Still the excel
> model got the wrong solution.
>
> This is still solution to Excel
>
> LOC1  LOC2    QUANTITY
> San Diego     New York        325
> San Diego     Topeka  275
> Seattle       Chicago 300
>
> No entry for Seatle to New York.
>
> Noli
>
>
>
>
> On 2/12/10, Andrew Makhorin <address@hidden> wrote:
>>> However, when converted
>>> the 3 models into CPLEX format, the 3 models are exactly the same,
>>> just ordering of the variables and constraints
>>>  (see converted models below).
>>
>> Not the same.
>>
>>> \* Problem: transp_odbc_sqlite3 *\
>>
>>> Minimize
>>>  cost: + 0.225 x(Seattle,'New_York') + 0.153 x(Seattle,Chicago)
>>>  + 0.162 x(Seattle,Topeka) + 0.225 x('San_Diego','New_York')
>>>  + 0.162 x('San_Diego',Chicago) + 0.126 x('San_Diego',Topeka)
>>
>>> \* Problem: transp_odbc_xls *\
>>> Minimize
>>>  cost: + 0.225 x('San_Diego',Chicago) + 0.162 x('San_Diego','New_York')
>>>  + 0.126 x('San_Diego',Topeka) + 0.153 x(Seattle,Chicago)
>>>  + 0.225 x(Seattle,'New_York') + 0.162 x(Seattle,Topeka)
>>
>> transp_odbc_sqlite3:
>> 0.225 x('San_Diego','New_York')
>>
>> transp_odbc_xls:
>> 0.162 x('San_Diego','New_York')
>>
>>
>