| Reading from excel: less cases than rows defined [message #77] |
Sat, 14 April 2007 10:58 |
 lgroenveld
Messages: 3
Registered: March 2007 |
Junior Member |
|
|
Possible problem
When multi-case parameters should be written to and read from MS Excel, the following problem may arise. In a typical situation, Quaestor will place one or more multi-case parameters in columns in the sheet. Other columns may then calculate their cell values based on this input, which are to be read by Quaestor.
When formulas are present in the calculating columns, but there is no input (e.g. more rows are defined than there are cases), cells will typically display 'Not a Number' or 'Divide by zero'.
When the outputscript of Quaestor is runned, cases for a parameter are retrieved until an empty cell is reached in the corresponding column. If the output telitab contains multiple variables (usually the case), the first parameter that is read determines the number of cases in the output telitab.
If this first parameter is a calculated column, Quaestor will eventually find a 'NaN' or 'Div by 0' message instead of an empty cell, if there are less input cases than rows defined in the calculating column. As this is no valid input, an error message is shown and the calculation is terminated.
Solutions
There are several solutions to this problem. The first will mostly be already fullfilled: let the first column to be read be an input variable. As this column was written by the inputscript, no formulas are present there and an empty cell is present below the last case. This is often the case, as the output telitab should contain calculated values for input data (that is also present in the telitab).
If, for some reason, only calculated columns are to be present in the output telitab, the excelsheet should be adjusted. The formulas might be placed in a 'IF' construction that should assign the value "" if no input data is present. Quaestor will then meet an empty cell after the last case.
Examples
The following example kb's should explain this. The communication with excel is used here to determine the resistance of different ships. Multiple case input data is placed in the excelsheet representing different ships. Economic speed, resistance and power are read from calculating columns in the sheet.
The first KB (read_excel_wrong.qkb) only reads the calculated columns, which are all defined by formulas in the sheet. The second KB (read_excel_right.qkb) reads the ship number (which is input) prior to the calculated columns.
Our top goal is Resistance# and input data is present in the dataset. In the process manager, just run a solution for Resistance# with 'All list values' selected. The excelsheet is embedded in the relation for Resistance#. There are 40 cases, but more rows are defined in the sheet.
If the first KB comes to the reading of parameters in excel, the following error is shown:
Error in EXCELOUTPUT (Rt(17))/"Rt": Error 2007
The second KB runs fine.
Please compare the two Resistance# outputscripts to see the difference.
|
|
|
Members
Search
FAQ
Register
Login
Home
