Hi!
I´m looking for a solution for a control check I´m doing. I have workbook1 with several hundred time series. Each column has a series header and each row has a date. The dates changes every day, removing one and adding the next one as each day passes. The columns changes as well, adding and removing time series as these are added and removed in my database.
In my control workbook I have for each row two dates, begining and end, a value to check for and one or several values that correspond to the headers in workbook1. These rows changes as well as Items are added and removed from the database.
Example
Workbook 1
A B C D
Date X Y Z
2016-01-01 1 20 72
2016-01-02 2 45 85
2016-01-03 3 25 130
2016-01-04 5 19 70
2016-01-05 7 20 69
.
.
2016-02-26
Workbook control
A B C D E F G H I
Item Begining date End date value1 Control value1 value2 control value2
1. 2016-01-02 2016-03-04 X 0,5 Y 40
2. 2016-01-03 2016-01-04 Z 75 NA NA
For the above example I want to check if the data points in the time series in workbook 1 at any date in the range has been below the control value.
so for Item 1 I want to check if value 1 has been below control value 1 in the range 2016-01-02 up until the last recorded value in the range for X in workbook 1. I only want the first value that fulfills the breach.
The result for item 1 would be:
value X NO
value Y YES 2016-01-03 25
The result for item 2 would be:
value Z YES 2016-01-04 70
As said next day, Y might have disappeared from the database and Z would be next to X in workbook one
What´d think? Any ideas how to solve this?
I´m looking for a solution for a control check I´m doing. I have workbook1 with several hundred time series. Each column has a series header and each row has a date. The dates changes every day, removing one and adding the next one as each day passes. The columns changes as well, adding and removing time series as these are added and removed in my database.
In my control workbook I have for each row two dates, begining and end, a value to check for and one or several values that correspond to the headers in workbook1. These rows changes as well as Items are added and removed from the database.
Example
Workbook 1
A B C D
Date X Y Z
2016-01-01 1 20 72
2016-01-02 2 45 85
2016-01-03 3 25 130
2016-01-04 5 19 70
2016-01-05 7 20 69
.
.
2016-02-26
Workbook control
A B C D E F G H I
Item Begining date End date value1 Control value1 value2 control value2
1. 2016-01-02 2016-03-04 X 0,5 Y 40
2. 2016-01-03 2016-01-04 Z 75 NA NA
For the above example I want to check if the data points in the time series in workbook 1 at any date in the range has been below the control value.
so for Item 1 I want to check if value 1 has been below control value 1 in the range 2016-01-02 up until the last recorded value in the range for X in workbook 1. I only want the first value that fulfills the breach.
The result for item 1 would be:
value X NO
value Y YES 2016-01-03 25
The result for item 2 would be:
value Z YES 2016-01-04 70
As said next day, Y might have disappeared from the database and Z would be next to X in workbook one
What´d think? Any ideas how to solve this?