transpose information in gdx to excel file

GAMS
1

Dear All,

I have model written in GAMS in which I write the result of the model to a GDX file and then use the gdxxrw.exe
to write the results into excel (see code below).

My question is the following, in excel I have currently the content in the following structure

1 2 3
122 1223 344

How can I make that (transpose) the content folows the following structure:

1 122
2 1223
3 344

loop (t,
inc.fx = s(t);
solve pfizer using nlp minimizing anncost;
execute_unload “results.gdx” x.L x.M
execute ‘gdxxrw.exe results.gdx var=x.L var=x.M rng=Sheet1!B1’
);

Thank you, best regards
Hugo

–
To view this discussion on the web visit https://groups.google.com/d/msg/gamsworld/-/V8S1ZFOCK5oJ.
To post to this group, send email to gamsworld@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.

2

Hi!
I am having the same issue.
Did you solve it? I’d appreciate your help :smiley:

Thanks!!!







On Wednesday, July 11, 2012 8:45:47 AM UTC-3, Hugo Caçote wrote:

Dear All,

I have model written in GAMS in which I write the result of the model to a GDX file and then use the gdxxrw.exe
to write the results into excel (see code below).

My question is the following, in excel I have currently the content in the following structure

1 2 3
122 1223 344

How can I make that (transpose) the content folows the following structure:

1 122
2 1223
3 344

loop (t,
inc.fx = s(t);
solve pfizer using nlp minimizing anncost;
execute_unload “results.gdx” x.L x.M
execute ‘gdxxrw.exe results.gdx var=x.L var=x.M rng=Sheet1!B1’
);

Thank you, best regards
Hugo

–
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

\

3

Hi,

You can define a new parameter to transpose the data. Please find attached an example.


Cheers,
Pedro



On Tue, Jan 29, 2013 at 3:19 PM, Lucy N wrote:

Hi!
I am having the same issue.
Did you solve it? I’d appreciate your help :smiley:

Thanks!!!







On Wednesday, July 11, 2012 8:45:47 AM UTC-3, Hugo Caçote wrote:

Dear All,

I have model written in GAMS in which I write the result of the model to a GDX file and then use the gdxxrw.exe
to write the results into excel (see code below).

My question is the following, in excel I have currently the content in the following structure

1 2 3
122 1223 344

How can I make that (transpose) the content folows the following structure:

1 122
2 1223
3 344

loop (t,
inc.fx = s(t);
solve pfizer using nlp minimizing anncost;
execute_unload “results.gdx” x.L x.M
execute ‘gdxxrw.exe results.gdx var=x.L var=x.M rng=Sheet1!B1’
);

Thank you, best regards
Hugo

–
Test.gms (11 KB)

4

Hi

You don’t have to define a new parameter in this case. Gdx gives you the possibility to choose the dimensions of the table in excel using “rdim” (row dimension) und “cdim” (column dimension).

Here is a small example of sending a 3-dimensional parameter to excel. Gdx allows you to change the format of the table as follows

set i /i1i3/,
j /j1j3/,
k /k1*k3/;

parameter
results(i,j,k) Results;

  • Initialize with some arbitrary numbers
    results(i,j,k) = uniform(0,1);

  • Write the parameter to a gdx file:
    execute_unload ‘results.gdx’, results;

  • Sending the parameter to excel without additional options:
    execute ‘gdxxrw.exe o=results.xlsx i=results.gdx par=results rng=sheet1!a1’ ;

  • In Excel the table will look like this

  •   		k1	k2

  • I1 j1 0.0343 0.4300

  • Setting rdim=3 (in the row you will find the 3 sets back, therefore r(ow)dim and not cdim) will give you the pivot table format:

execute ‘gdxxrw.exe o=results.xlsx i=results.gdx par=results rng=sheet1!a1’ ;

  • i1 j1 k1 0.0343

  • i1 j1 k2 0.4300

  • …

  • You can combine the rdim and cdim to get the table format you want.

Cheers

Renger

Von: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] Im Auftrag von Pedro Javier Ramírez Torrealba
Gesendet: Dienstag, 29. Januar 2013 20:26
An: gamsworld@googlegroups.com
Betreff: Re: transpose information in gdx to excel file

Hi,

You can define a new parameter to transpose the data. Please find attached an example.


Cheers,
Pedro


On Tue, Jan 29, 2013 at 3:19 PM, Lucy N wrote:
Hi!
I am having the same issue.
Did you solve it? I’d appreciate your help :smiley:

Thanks!!!







On Wednesday, July 11, 2012 8:45:47 AM UTC-3, Hugo Caçote wrote:
Dear All,

I have model written in GAMS in which I write the result of the model to a GDX file and then use the gdxxrw.exe
to write the results into excel (see code below).

My question is the following, in excel I have currently the content in the following structure

1 2 3
122 1223 344

How can I make that (transpose) the content folows the following structure:

1 122
2 1223
3 344

loop (t,
inc.fx = s(t);
solve pfizer using nlp minimizing anncost;
execute_unload “results.gdx” x.L x.M
execute ‘gdxxrw.exe results.gdx var=x.L var=x.M rng=Sheet1!B1’
);

Thank you, best regards
Hugo
– PEDRO JAVIER RAMÍREZ TORREALBA Ingeniero Civil Eléctrico PUC MSc en Ingeniería Eléctrica PUC Londres, REINO UNIDO Celular: +44-(0)75-8069-3119