Exclude euqation based on parameter from excel

Problems with modeling
Post Reply
Robert
User
User
Posts: 7
Joined: 1 year ago

Exclude euqation based on parameter from excel

Post by Robert »

I'm trying to exclude equation e1(i) from a model at compilation time. The value the exclusion depends on is written in an Excel-file.

See example:

Code: Select all

* Definitions
set i /i1/;
scalar pEnable /0/;
variable obj;
equation e1(i), e2(i);
$inlineCom { }


* Mathematical Formulation

e1(i){$pEnable}.. obj =e= 0;
e2(i).. obj =e= 0;

* Read range "pEnable" (specified from A1:D1) from "Exclude.xlsx" Excel-file that has the following content
* A1: pEnable
* B1: =
* C1: YES
* D1: ;
$call xls2gms m i="Exclude.xlsx" r1=pEnable o1=tmp_pEnable.txt
$include tmp_pEnable.txt

display pEnable;


* Model definition

$set SQUEEZELIST ""
$ifE pEnable=0 $set SQUEEZELIST %SQUEEZELIST% - e1

$log Building model with all %SQUEEZELIST%
model test / all %SQUEEZELIST% /;
solve test min obj using lp;
However, this does not work. Even when pEnable is set to YES (or 1) in the Excel-file, the model is still built with /all - e1/. (pEnable is initialized with pEnable /0/ on top).
The display statement shows the correct value of "1".

As of my understanding, this might be because of compilation and execution time. Altough it says that xls2gms is run during compilation time, it does not set the value for pEnable.

Is there any solution to set the value of pEnable based on the value in the Excel-file to exclude the equation?
(I know the equation could be ignored with e1(i)$[pEnable], but I would prefer to not even include the equation if pEnable is set to 0 in the Excel-file).

Thanks.
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Exclude euqation based on parameter from excel

Post by bussieck »

Robert, don't use xls2gms. This is an ancient tool and the web site recommends dropping the tool in favor of gdxxrw (see https://www.gams.com/latest/docs/T_XLS2GMS.html). Nowadays there is GAMS Connect (see https://www.gams.com/latest/docs/UG_GAMSCONNECT.html) for platform independent Excel processing. In any case the way you use xls2gms, it produces an execution time statement. As I mentioned in my last post you need the value of pEnable at compile time to exclude the equation from the model statement. There is no issue with at least gdxxrw getting a scalar at compile time:

Code: Select all

* Definitions
set i /i1/;
scalar pEnable, qEnable;
$onEcho > gdxxrw.in
i=Book1.xlsx
o=Book1.gdx
par=pEnable rng=Sheet1!B2:B2 rdim=0 cdim=0
par=qEnable rng=Sheet1!C2:C2 rdim=0 cdim=0
$offEcho
$call.checkErrorLevel gdxxrw @gdxxrw.in
$gdxIn Book1.gdx
$load pEnable qEnable

variable obj;
equation e1(i), e2(i);
$inlineCom { }


* Mathematical Formulation

e1(i){$pEnable}.. obj =e= 0;
e2(i).. obj =e= 0;

* Model definition

$set SQUEEZELIST ""
$ifE pEnable=0 $set SQUEEZELIST %SQUEEZELIST% - e1

$log Building model with all %SQUEEZELIST%
model test / all %SQUEEZELIST% /;
solve test min obj using lp;
The Excel file (here Sheet1) looks as follows:
image.png
image.png (3.36 KiB) Viewed 2504 times
-Michael
Robert
User
User
Posts: 7
Joined: 1 year ago

Re: Exclude euqation based on parameter from excel

Post by Robert »

Thank you for the explanation.

Actually I would like to either use gdxxrw or PandasExcelReader with GAMS connect, but my tables in Excel are already formated in a way, that the importing tool should consider the content of a spreadsheet as Text.
Examples:
table3.png
table3.png (5.88 KiB) Viewed 2477 times
table1.png
table1.png (3.05 KiB) Viewed 2477 times
table2.png
table2.png (4.25 KiB) Viewed 2477 times
Than ranges are specified in Excel with the Name Manager and xls2gms is used to read the content of the ranges as following:

Code: Select all

file TMP / tmp_Example.txt /
$OnEcho  > tmp_Example.txt
   r1=     indices
   o1 =tmp_indices.txt
   r2 =    param
   o2 =tmp_param.txt
   r3 =    factories
   o3 =tmp_factories.txt
$OffEcho

$call xls2gms m i="Example.xlsx" @"tmp_Example.txt"

sets
$include tmp_indices.txt
;

*general parameters
$include tmp_param.txt
;

table	tFactories(f, *)
$include tmp_factories.txt
;
So far I have not found a way to do that with other tools like gdxxrw or PandasExcelReader.

The data input in the excel format is based on other tools. So changing the format of the Excel-files is not really an option.

If there is a way to use gdxxrw or PandasExcelReader to interpret the input as text, I would be very happy to know about it.

Thanks,
Robert
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Exclude euqation based on parameter from excel

Post by bussieck »

If you can't change the Excel input then you are stuck. Since the Excel input (independent what tool reads it) has an execution time statement to set the scalars. You need the value of the scalar at compile time. Some of the Excel input is compile time input, perhaps you can change the "Definitions" of parameters also to compile time statements:
image.png
image.png (2.92 KiB) Viewed 2412 times
and then read under a declaration of scalars. A desperate attempt to get the scalars at compile time is to execute the GAMS instructions from Excel at compile time:

Code: Select all

$onEcho > x.gms
scalar pEnable, qEnable;
$call xls2gms m i="Exclude.xlsx" r1=pEnable o1=tmp_pEnable.txt
$include tmp_pEnable.txt
$offEcho
$call.checkErrorLevel gams x.gms gdx=x
scalar pEnable, qEnable;
$gdxIn x
$load pEnable qEnable
-Michael
Robert
User
User
Posts: 7
Joined: 1 year ago

Re: Exclude euqation based on parameter from excel

Post by Robert »

Tanks for your time and your help.

It helped a lot.
Post Reply