Handle null value in array

[dswitzer]

Using classic ASP/VBScript with SQL back-end,

I have a dataset like:

Month F1 F2 F3
Month1 100 110 100
Month2 105 110 100
Month3 111 102
Month4 110 112 102


I read these values into an array and then graph them through OWC -- but my problem is that the array for F1(3) sees a null value -- and then the dataset collapses and my data for graphing looks like this:

Month F1 F2 F3
Month1 100 110 100
Month2 105 110 100
Month3 110 111 102
Month4 112 102

I need to be able to set that value (Month3,F1) to a null....zero won't cut it because I don't want to graph a zero if the data is missing....

Any ideas?
Please let me know if I can provide additional data.

Thanks.

 

[tsuji]

Hello dswitzer,

Just use =null, as demonstrated here.

x=null
wscript.echo x & vbcrlf & typename(x) & vbcrlf & vartype(x)

regards - tsuji

 

[mrmovie]

If Not IsNull(aArray(0)) Then

End If

 

[dswitzer]

Thanks -- we started working on this again and it looks like the array is getting the null value correctly (which I wasn't sure was the case originally), but the charting component collapses the nulls when graphing. So it must be in the OWC charting tool somewhere.

I don't get it...if I copy the dataset:

Month F1 F2 F3
Month1 100 110 100
Month2 105 110 100
Month3 111 102
Month4 110 112 102

into Excel and graph -- the graph charts the null value in month 3 and the 110 value in month 4 -- which is what I want...yet the OWC tool ignores the null placeholder and collapses the values in Field 1....like this:

Month F1 F2 F3
Month1 100 110 100
Month2 105 110 100
Month3 110 111 102
Month4 112 102

Very frustrating...Any ideas?

 

[mrmovie]

how about when you array is built check for null values, if the IsNull then dont write a null value to the array but instead write and empty string "" or write an integer 0
????

 

[dswitzer]

Yep - appreciate that. I tried that and everything is cool in the array -- I see that the null is showing in the array as specified - now the graph is still another story...

My original title should be changed from:

Handle null value in array

To:

How can I graph a null value in OWC Charting tools?

OWC completely ignores the null value as a placeholder -- truncating the array for those values -- so the graph is wrong. Unfortunately I don't think I can show the graphs but the datasets I showed show what I mean. The first table is the actual dataset (and the array) -- this is fine. The second dataset (note that in Field1 the Month4 value is moved to Month3) is what OWC actually charts. The only way I have found around this is to put zeroes in there as placeholder....but a zero is not a null and the graph looks horrible. How can I graph (or not graph) the missing data point without forcing to zero?

 

[mrmovie]

i guess you need to determine what character or type the OWC tool will accept as a 'missing' point. it doesnt like 0, or Null, I guess you are left with "" or some other special character.

 

[dswitzer]

Yeah - I found a link where Microsoft says that it will not work....yet, clearly, you can do this in Excel with no problem...but their OWC support is so weak...

I found someone with a workaround building multiple series to circumvent the missing data...not a solution, definitely a workaround....I'll try to decipher this.

Thanks.