search

OpenCDSS / cdss-app-tstool-main

Colorado's Decision Support Systems (CDSS) TSTool application main program
GNU General Public License v3.0
1 stars 1 forks source link

SWSI Tool - TSTool not writing to Excel file #217

Open smalers opened 2 years ago

smalers commented 2 years ago

Subject of the issue

TSTool is having errors writing SWSI (Surface Water Supply Index) output to the Excel file.

Environment

Steps to reproduce

I was provided with the current SWSI files for September of 2021. Running through the analysis steps has errors writing to the Excel files used to check input.

smalers commented 2 years ago

There are at least two problems:

DateTime Excel integration

Awhile back I fixed bug in Excel integration where date/time objects were not being handled well. This caused an issue in later releases because a TSTool table has a column defined as type String but a DateTime is put into the column. Internally, the code was trying to cast to (String) and because the types were different this failed. I changed to toString() and it works.

Recent Period is Zero Length

Because of the year being analyzed, the "RecentPeriod" is zero length. This is the period after the historical period but before the current year. The math in the Config worksheet results in RecentPeriodEndDate being less than RecentPeriodStartDate. The time series iterators complain that the start is before the end. Some possible fixes are:

  1. Live with the warnings/failures. This is not great because ideally the processing should be without problems.
  2. Add If commands in the workflow to check for this situation and not try to calculate statistics in the recent period, since there is no recent period. I may need to enhance TSTool because I'm not sure if If hands date/time comparisons especially when property types can get mixed up between string and date/time. It would be helpful to enhance the Results / Properties table to indicate the type of the property to help with troubleshooting. The downside to this is that what if the period is really messed up and needs to be fixed? Option 3 could handle that but has other repercussions.
  3. Change the formula in the Config worksheet to set RecentPeriodStartDate and RecentPeriodEndDate to be a different value in this case, even blank. The TSTool workflows would then need to react to that in the logic. Maybe need another configuration property that is RecentPeriodNumMonths and if zero the workflow can handle.

This is perhaps a bigger issue because it also impacts the recent period annotations on graphs so would need to handle there to make sure that products are not impacted.

This is a somewhat big problem

smalers commented 2 years ago

I'm building TSTool 14.3.0.dev1 now for Tracy to try.