# Decision Optimization

View Only

## Export Tuple to Excel/ For-loop questions

• #### 1.  Export Tuple to Excel/ For-loop questions

Posted Sun April 24, 2022 10:11 PM
Edited by Jia Zhong Mon April 25, 2022 07:44 AM
I am still learning the tricks of coding the model. I am still digesting the tricks of OPL.

1. I have a variable "Lifetimecost_est" having multiple dimensions. I use tuple to export the data to excel. It is a data set of 16×7×4×3×24 (32,256). I checked many answers from the community, and was confused by the setup for the excel range. I am not sure for a tuple like this, how could I set up the range of the excel correctly? I have now 24 columns (segments) and 50,000 rows. But still got the errors "".

``````tuple Lifetimecost_tup{
int t;
string cost;
string vehicle;
string vmtgrp;
string segment;
float value;
}
{Lifetimecost_tup} Lifetimecost_exp= {<i,j,k,z,l,Lifetimecost_est[i][j][k][z][l]> | i in t, j in cost, k in vehicle, z in vmtgrp,l in segment};​

2. I have a parameter needs to multiply a yearly -0.5% growth rate starting 2016. It is also a multi-dimensional data MileB_seg[ti][s][vmt], ti is the year set. Math.pow((1-0.005),(ti-2016)) applies the compounded annual growth rate.
What I did is putting it in the script

``````float MileB_seg[t][segment][vmtgrp];
execute{
for (var ti in t) {
MileB_seg[ti][s][vmt]=MileB_seg[ti][s][vmt]*Math.pow((1-0.005),(ti-2016));
}}``````
However, I found the MileB_seg still does not change with the time. Wondering what should I code it correctly to apply this compounded growth rate?

Thanks a lot!

------------------------------
Jia
------------------------------

• #### 2.  RE: Export Tuple to Excel/ For-loop questions

Posted Mon April 25, 2022 04:16 AM
Dear Jia,

About question 2.  You seem to use the same array cell in both sides of your assignment. In your loop you are doing x = x Math.pow(...) with x which is not initialized.
I don't think it is what you want.
I hope this helps.
Cheers,

------------------------------
Renaud Dumeur
------------------------------

• #### 3.  RE: Export Tuple to Excel/ For-loop questions

Posted Mon April 25, 2022 03:20 PM
Edited by Jia Zhong Tue April 26, 2022 08:14 AM
Hi Renaud,

For question 1, the simplified example is as follows:
``````//In *.mod file:

range t=2016..2031; //
{string} cost=...;
float Lifetimecost_est[ti in t][c in cost]=ti;

int t;
string cost;
float value;
}

execute {

};

//In *.dat file
cost={purchase,maintanence,fuel,detour,range,wait,idio};
SheetConnection Outputs("C:\\Users\\jzhong17\\OneDrive - azureford\\Documents\\CPLEX\\OPL project\\Outputs.xlsx");

This simplified model will only generate a 14×7data, but I leave a F2:zz500 space in excel still generate the error of "Description Resource Path Location Type
Exception from IBM ILOG Concert: excel: range width does not fit the arity of the tuple. Test Unknown OPL Problem Marker".

For question 2, I did have initialize the MileB_seg[ti][s][vmt]​ before this statement in a different for-loop, and MileB_seg[ti][s][vmt] is the same across ti set. Even if I have coded the math.pow() statement, MileB_seg still remain the same.
What I concerned about is that in the script I only apply the for-loop for year "ti", not for other sets (segment and vmtgrp), but I still use the index for these set. ​

------------------------------
Jia
------------------------------

• #### 4.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 05:12 AM
Dear Jia,

Your tuple has three elements so your excel write range should cover 3 excel columns.
However, vertically the range could go further than the 7*16 rows,  but extra rows for which no tuple exist will be ignored.

Cheers,

------------------------------
Renaud Dumeur
------------------------------

• #### 5.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 07:57 AM

Hi Jia,

You could also redirect the output from .mod to either .txt or csv file.

In the attached example, I am solving a version of Sudoku with CP, reading the data from a csv file, and writing the output into csv file.

You can run it on your machine but renaming the path of the model (saved as attached rtf file).

Best regards,

 Nourredine Hail, PhD in Applied Mathematics Senior Operations Research & Data Scientist Data Analytics Governance team Canadian Tire Corporation 2111 Steeles Avenue East, Brampton, ON, L6T4L5 Phone: 905.792.5983   nourredine.hail@cantire.com

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

Attachment(s)

SUDOKU.rtf   6 KB 1 version

• #### 6.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 08:18 AM
Thank you, Nourredine. But I did not see the attached example. Can you please upload it again?

Thanks,
Jia

------------------------------
Jia
------------------------------

• #### 7.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 08:21 AM

Jia,

The files have been uploaded as attached files (see my screenshot).

Thanks

 Nourredine Hail, PhD in Applied Mathematics Senior Operations Research & Data Scientist Data Analytics Governance team Canadian Tire Corporation 2111 Steeles Avenue East, Brampton, ON, L6T4L5 Phone: 905.792.5983   nourredine.hail@cantire.com

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

• #### 8.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 08:22 AM

Click on view attached to see them.

 Nourredine Hail, PhD in Applied Mathematics Senior Operations Research & Data Scientist Data Analytics Governance team Canadian Tire Corporation 2111 Steeles Avenue East, Brampton, ON, L6T4L5 Phone: 905.792.5983   nourredine.hail@cantire.com

"Anyone who stops learning is old, whether at twenty or eighty. Anyone who keeps learning stays young. The greatest thing in life is to keep your mind young."  Henry Ford

• #### 9.  RE: Export Tuple to Excel/ For-loop questions

Posted Mon April 25, 2022 04:19 AM
Dear Jia,

About question 1, please provide a minimal model and data so that we can reproduce your problem.

Cheers,

------------------------------
Renaud Dumeur
------------------------------

• #### 10.  RE: Export Tuple to Excel/ For-loop questions

Posted Tue April 26, 2022 11:09 AM
Edited by Frederic Delhoume Tue April 26, 2022 11:10 AM
The export to Excel code must be in a dat file, and is called after a  solve.

//sample.mod
tuple toto {
int num;
string str;
}

{toto} mytuple = {
<10, "text1">,
<20, "text2">
};

execute {
mytuple;
}

//sample.dat
SheetConnection sheet("exported.xls");
mytuple to SheetWrite(sheet, "A1:B2");

running this updates the xls file (that file must exist as it will not be created by opl).

------------------------------
Frederic Delhoume
------------------------------

• #### 11.  RE: Export Tuple to Excel/ For-loop questions

Posted Wed April 27, 2022 11:36 AM
Thanks Frederic!

------------------------------
Jia
------------------------------

• #### 12.  RE: Export Tuple to Excel/ For-loop questions

Posted Wed April 27, 2022 03:03 PM
``````//*.mod
range t=2016..2031; //
{string} cost=...;
float Lifetimecost_est[ti in t][c in cost]=ti;
float data[ti in t]=ti;

execute{
for (var ti in t) {
}
};

//*.dat
cost={purchase,maintanence,fuel,detour,range,wait,idio};​``````

Hi Renaud, here is the example. I have this multi-dimensional parameter Lifetimecost_est, I would like to let it degrades by 5% annually. In this simplified example, the values are the same across set c. But the idea is let the value change by 5% for all cost categories.
The above statement will yield error message. I need help in correcting this code. I have been searching online for a while about the solution of this.

Thanks a lot!

------------------------------
Jia
------------------------------