pocketquery line chart question

[Ming Ho]

my query results have some null values. i got an error about "All series on a given axis must be of the same data type". any idea how i can fix this? i tried setting interpolateNulls, it didn't seem to help.

[Felix Grund (Scandio)]

Hi Tim, this is really hard for me to reproduce. Is this sensitive data? Maybe you could provide a database backup so I can reproduce better.

[Tim Kerns]

Felix,

I'm using the version you are.  Here is what is looks like in table format.

image2016-5-4 12:18:50.png

When I do it as a line chart, I get the error.

image2016-5-4 12:20:17.png

Here is the SQL (using MS SQL 2012): 

image2016-5-4 12:27:33.png

[Felix Grund (Scandio)]

Hi Tim,

This is really strange. It works perfectly for me. Maybe you can think about what's different for you. I'm using PocketQuery 2.1-m6. I'm using the sample MySQL "World" database ( as dump file).

This is my statement:

SELECT Name, LifeExpectancy
FROM Country
WHERE Continent = 'Oceania'

This is my template:

<script>
PocketQuery.chart('LineChart');
</script>

Now, most countries in Oceania have a number in the column LifeExpectancy. But some of them have NULL. For the ones with NULL, there is a gap in my chart:

screen-pq-line.png

Maybe you can find out what's wrong with this?

Regards, Felix (Scandio)

[Tim Kerns]

Felix:  No worries, but unfortunately, this did not appear to fix the problem.

[Felix Grund (Scandio)]

Hi Tim! Sorry for the harsh delay, this was not intended. We're about to release 2.1. Can you try if this early release of PocketQuery 2.1 works for your use case? Thanks!

[Tim Kerns]

Thanks Felix. Looking forward to the next release!

[Felix Grund (Scandio)]

Hi Tim! The issue is actually in PocketQuery: as soon as a value of a column for the line chart is not a number (in this case null is not a number), the type for the whole column will become string. In that case, Google Charts won't work with the input submitted by PocketQuery. I added this as a bug in PocketQuery and I will target it with the next release.

[Tim Kerns]

Felix, Thanks for the suggestion. I have a set of data like this: | Date | Low | Med | High | Overall | | 12/10 | 0.81 | 0.75 | 1.00 | 0.85 | | 12/11 | 1.00 | 1.00 | 1.00 | 1.00 | | 12/14 | 1.00 | NULL | NULL | 1.00 | | 12/15 | 1.00 | 1.00 | 1.00 | 1.00 | I'm looking to do a line graph so that the null values are simply omitted in the graph. Using a WHERE clause would eliminate a row with nulls altogether. I don't want NULL represented as zero (0) either as that would not be an accurate representation in the graph.

[Felix Grund (Scandio)]

Have you tried to add a WHERE ... IS NOT NULL clause?

[Tim Kerns]

This appears to still be an issue. Is there a fix for this?

[Felix Grund (Scandio)]

Hi! Is your issue still recent? If so, I'll have another look at it.

[Christian Koch]

I think this will fix your problem http://stackoverflow.com/questions/1441333/mysql-typecasting-null-to-0

[Ming Ho]

thanks Christian. but my intend is not to plot anything if the value is null, rather than converting null to 0. sorry for not making myself clear.

[Christian Koch]

I think it's the best to remove the record through the sql query.