|
 Data gaps in database time series |
| Posted by Chris on Jul-18-2011 18:22 |
|
Hi
I have a chart pulling back various data columns from SQL including a datetime column (rows at 20min intervals) to build a set of line layers. However, I have some data missing in SQL (say several hours of no data) and at the moment an interpolated line is drawn between the data points before and after the data gap. I am just wondering how I could force the break/highlight the gap with no data.
I guess ideally need to insert cd.NoValue against missing datetime rows (though not present at the moment in the SQL table) or maybe run a second SQL query listing gaps and then using that as a new data series.., just wondered what the art of the possible might be?
Thanks
Chris |
| Re: Data gaps in database time series |
| Posted by Peter Kwan on Jul-18-2011 21:17 |
|
Hi Chris,
You would need to tell ChartDirector that there is a gap by inserting a NoValue point at the position of the gap. Otherwise, there is no way to know if there is a gap anywhere. (The data points can naturally be unevenly spaced. Even if all data points happen to be equally spaced, it is still possible there are gaps among all points. There can also be gaps before the first valid data point, or after the last valid data point.)
If you know in advance that the data points must be at certain positions (like at 10:00, 10:20, 10:40, ....), it means the timestamps are not based on your data records. In this case, a common algorithm is as follows:
(a) Create the timestamps array using the pre-defined timestamps. Create the data arrays (the same size of the timestamps array), and initialize them to NoValue.
(b) Read the records from the database. For each record, put the data in the appropriate position in your arrays.
In this way, if a date/time position has data, it will be filled with the data at step (b). Unfilled positions will become NoValue.
Hope this can help.
Regards
Peter Kwan |
| Re: Data gaps in database time series |
| Posted by Chris on Jul-18-2011 21:29 |
|
Peter
Thanks for the ideas. Are you talking about using data0 = Array() functionality and adding in the SQL data over the top, or using say ASP array functions to build the correct data then assign data columns. Can you use Set dbTable = cd.DBTable(MyArray) on an ASP array or is there another technique?
Thanks
Chris |
| Re: Data gaps in database time series |
| Posted by Peter Kwan on Jul-19-2011 02:13 |
|
Hi Chris,
If you are using VB6/ASP, there is a feature in the cd.dbTable method that allows you to specify the array index of the row. For example, if you have 5 rows of data, and their indexes are 0, 1, 4, 5, 6. ChartDirector cd.dbTable will create an array of 7 segments, and will fill the missing indexes (2 and 3) with cd.NoValue.
To use the above feature, you need to be able to write an SQL that can return the array index. This should not be difficult if you know the start date/time of your data, and your data are regularly spaced (say 1 point every 20 minutes). You should be able to use SQL date/time expression to compute the array index.
For more details, please refer to the documentation on "DBTable" (you may look for "DBTable" in the ChartDirector documentation index).
If the above does not fit your needs, you may declare the array yourself, initialize all unknown values to cd.NoValue, then fill the array with the SQL results. In VB6/VBScript, the statement to declare an array is Dim or ReDim. (In VB6/VBScript syntax, the Array(...) function is only for defining an array literal.) The method to read SQL results depend on the database API framework you are using. For ADO, the code is something like:
ReDim myTimeStamps(noOfTimeStamps - 1)
ReDim myData(noOfTimeStamps - 1)
For i = 0 To Ubound(myTimeStamps)
myTimeStamps(i) = ... initialize the timestamps to the predefiend values ...
myData(i) = cd.NoValue
Next
'rs = your query result set
Do While Not rs.EOF
index = .... compute the array index based on the timestamp of the row ....
myData(index) = .... read the data value from the row ....
rs.MoveNext
Loop
Hope this can help.
Regards
Peter Kwan |
| Re: Data gaps in database time series |
| Posted by Chris on Jul-21-2011 16:46 |
|
Hi Peter
I have come up with a way to generate my all inclusive dataset in SQL and now have certain rows of data with a datetime but null data columns (processing nulls in the graph gives unsightly vertical drop lines to the axis). I thought I could just substitute nulls for a string - isnull(convert(varchar(30), w.Hm0),'cd.NoValue') within the SQL statement and then when the column is called via col1 = dbTable.getCol(1) and then Call layer.addDataSet it would recognise the missing data points. However.... the error I get on graph generation is
ChartDirector error '800a8000'
Error converting argument 1 to type class DoubleArray
The SQL column I convert to string for nulls is a float format by design if that helps. So I guess the question is - is there a way to format the SQL output to trigger cd.NoValue for nulls?
Thanks
Chris |
| Re: Data gaps in database time series |
| Posted by Peter Kwan on Jul-21-2011 19:26 |
|
Hi Chris,
The cd is a VB/VBScript variable. It is not visible to SQL. (In fact, all variables in VB/VBScript cannot be used in SQL, because SQL is another language, and it runs in the database server, which may not even be in the same computer.)
In your SQL, you are actually using 'cd.NoValue', which is different from cd.NoValue. Even in VB/VB6, they are different and unrelated. For example, the following two lines are different:
x = "cd.NoValue"
x = cd.NoValue
For your case, you may use the VB/VBScript function IsNull to determine if something is null, then change it to NoValue. For example:
'Do not change anything in the SQL
col1 = dbTable.getCol(1)
'Change null to cd.NoValue
For i = 0 To Ubound(col1)
If IsNull(col1(i)) Or 0 = col1(i) Then col1(i) = cd.NoValue
Next
Hope this can help.
Regards
Peter Kwan |
| Re: Data gaps in database time series |
| Posted by Chris on Jul-21-2011 21:04 |
|
Peter
Works a treat, I wasn't sure about breaking apart col1 etc. I am also trying to highlight the gaps with a block shade - by creating two lines by essentially inverting the null logic and then using c.addInterLineLayer (), however it doesn't seem to come out as expected and but instead shows an interesting mix of line and block positioning. See the attached screenshot. I assume this is the best way to do this or is there a better way when dealing with cd.novalues...?
Thanks
Chris
|
| Re: Data gaps in database time series |
| Posted by Peter Kwan on Jul-22-2011 02:38 |
|
Hi Chris,
If I were you, if I need to highlight the gap, I may just use a dashed line (and/or a line of different color or line width) to join the gap. This can be archived by using the LineLayer.setGapColor API. For example:
Call myLineLayer.setGapColor(c.dashLineColor(&Hbbbbbb), cd.DashLine)
For your current chart, I am not sure exactly how you can generate the chart. You mentioned it is achieved by "by creating two lines by essentially inverting the null logic", so I assume you are trying to draw the null points. But the null points by definition has no value, so how can you determine the coordinates to use?
There are other methods, such as by scanning your col1 to determine the starting and ending points of each gap, and add a zone for it (using Axis.addZone). You may also create disjointed line segments to join the starting and ending points, and then fill between them with colors.
Hope this can help.
Regards
Peter Kwan |
| Re: Data gaps in database time series |
| Posted by Chris on Jul-25-2011 22:40 |
|
Hi Peter,
Thanks for the suggestions, went for an additional calculated SQL column with a constant value for null data points and then used a standard line layer with a very thick line and custom marker image to boot. On the subject of markers the following doesn't seem to play ball - Call layer.addDataSet(col4, , "Tz").setUseYAxis2().setDataSymbol(cd.CircleShape,1), works fine without .setUseYAxis2().
Chris |
| Re: Data gaps in database time series |
| Posted by Peter Kwan on Jul-26-2011 01:18 |
|
Hi Chris,
The method you use (by replacing null points with a fix value, and by replacing non-null points to null) will not produce the correct gaps.
For example, suppose you have 5 points:
(10, 20, null, 30, 40)
The above is two line segments, joining (10, 20) and (30, 40). There is a gap between (20, 40). Note that the gap starts and ends at valid points "20" and "40".
If you replace null with a fix value (say 100), and non-null values with null, the data becomes:
(null, null, 100, null, null)
The above does not contain any line at all and is not the gap anywya. (There is only one point, and you need at least two points to create a line segment.)
So instead of reversing the null points, I suggest you to use an algorithm to scan through your data to identify the starting and ending points of the gap (as mentioned above, they are "normal" points). Then you may use Axis.addZone to add a zone for each gap.
For "Call layer.addDataSet(col4, , "Tz").setUseYAxis2().setDataSymbol(cd.CircleShape,1)", note as according to ChartDirector documentation, both setUseYAxis2 and setDataSymbol are method of the DataSet objects. So you need to use a dataSet object to call the method (or you may use a function that returns a dataSet object). The code should be:
Set d = layer.addDataSet(col4, , "Tz") 'This is the dataSet object
Call d.setUseYAxis2()
Call d.setDataSymbol(cd.CircleShape,1)
Hope this can help.
Regards
Peter Kwan |
|
Forum Home
Search
Message List
Post Message
(Chris)