Sewage Spill Counter

Hi

I have a high level project idea, that I suspect is possible to do in Knime, however, I am not sure how it could be accomplished.

For reporting purposes we are required to count the number of sewage spills that are recorded through flow monitors recording sewage flows at various sites. Counting needs to be done using two separate methods. One is a straight count of spills the other is using a 12/24 method.

1. Straight count of spills

The workflow would need to go down the rows for each SITE column and count each block of data when the value goes over 100 (e.g. the first time it goes over 100 and then drops back to 100 or below would count as 1. The second time it did this would count as 2 and so on until the last row of the column). Missing data values would need to be included in the count block as we could not prove the spill level dropped back to 100 or below.

Once the spills for one column have been counted, it would loop to the next column and repeat the process.

2. 12/24 count method

Spill counts also need to be reported using the following method:

  1. Start counting when the first spill occurs (> 100 as above).

  2. Any spill (or spills) in the first 12-hour block are counted as one spill (so could go above and below 100 several times within the 12 hours but would only be counted as 1).

  3. Any spill (or spills) in the next, and subsequent 24-hour blocks, are each counted as one additional spill per block.

  4. Continue counting until there’s a 24-hour block with no discharge.

For the next discharge after the 24-hour block with no discharge, you begin again with the 12-hour and 24-hour block spill counting sequence.

Once the spills for one column have been counted, it would loop to the next column and repeat the process.

Output file

The output file would need to be a data table in the following format.

SITE NAME NUMBER OF SPILLS NUMBER OF SPILLS (12/24 method)

Site1 x a

Site2 y b

Site3 z c

Example Spill Data.xlsx

Would this be possible? Looking for some possible solution examples or a high level proposal of how it could be done.