How is a NULL value returned from a fpmi.db.runQuery represented, and can I test for it somehow?
The reason for my question is that the Bar Chart component seems unhappy when passed NULL values converted by fpmi.db.toDataSet and I would like to substitute the NULL for something that can be interpreted by the Bar Chart.
Regards
/T
one approach is to add the following to your query:
WHERE col IS NOT NULLDo you need to view the NULL values as data?
In code, I think the line to check for nulls is something like:
if x is None:
do something
The word None is the jython interpretation of null.
Hope this helps,
Colby,
Thank you for your answer. Unfortunately I was unable to get it working. The if never triggered.
I managed to get the following working:
if str(Col1Dat[i][1]) == "NaN":I understand that this is not elegant but I was unable to find out what is stored in those items that have NULL value returns from the SQL call, however it seems stable to convert whatever is stored to a string and compare with “Nan”, which I presume stands for “Not a Number”.
I guess there are some who wonder why I do this at all. The reason is that I have several (thousands) of different time series data that I want to be able to study and compare using Table, Chart and Bar Chart components. Unfortunately there are gaps in my data due to the fact that equipment is shut down or otherwise off line from time to time. In order to compare the information I need to fill out the gaps so that all samples are represented. Carl devised a way to get the SQL server to fill in for me using a separate table containing only sampling instances and joining. This works perfectly and results in missing samples being represented by NULL values (whatever they are) from the database. Those NULL:s show up as rectangles in the Table component which is OK. I have not had time to study the Chart component behaviour but I noticed that the Bar Chart component fails miserably when fed NULL values so I need to exchange them for something that the component can accept.
Sorry for the loooong post!
Regards
/T
edit - Colby’s post should have used ‘==’ instead of ‘is’
if x == None:
do something
Torulf,
A few things:
The new Easy Chart component, which is a more powerful time series line chart, displays NULL values as breaks in the line. You can use a bar graph renderer for a pen, but keep in mind that we’re still referring to a time series graph. The Easy Chart will be released with FactoryPMI 2.0. Release candidate 5 is currently available for download in the Integrator portal. The production version will be coming out shortly.
I’ll look into making the bar chart component more NULL resilient. Unfortunately it might end up like the Box and Whisker chart where it simply rejects dataSets that contain NULLs.
I’ll also look into the possibility of getting you a Jython function to do what you’re looking for. It might be augmenting fpmi.db.toDataSet to accept an optional parameter to substitute NULL values, a new function to work on DataSets, etc.
Nathan,
The Chart component handles “Nan” just fine with nice breaks in the plotted lines. For the moment I have taken care of the problem with the Bar Chart by substituting the “Nan”:s with (-1) which is a value that I do not have in my data.
For general use I think your extension to fpmi.db.toDataSet sound like a good idea.
I have a couple of things that I have noticed while playing that would extend the usability of the package but I will post them in the appropriate forum when I have a better understanding of FactoryPMI.
Regards
/T[/b]
I’m glad you found a workable solution. I would like to hear about your other requests. Posting them in the right forum would be perfect as you formalize the question. In the meantime, if you email them in the form of a list or questions, I can point you in the right direction if they are implemented, or get them into our system as requests if they’re not implemented and make sense to add.
Nathan,
I have sent you an email earlier today. I have tried emailing support a couple of days ago but Carl Gould did not see it. Can you check if you have received anything, please.
Regards
/T
I’ll forward the email to Carl and respond to you.
Torulf,
Glad you got the LEFT JOIN working. The comparison to “NaN” is perfectly acceptable, and does stand for “Not a Number” as you suspected.
Not to add to the confusion, but an even easier solution would be to simply have used a COALESCE in your query.
For instance, SELECT ColA, ColB, COALESCE(ColC, -1) as ColC FROM Table A LEFT JOIN Table B ON ....
The coalesce function returns the first non-null value it finds. Very handy.
Hope this helps,
Carl,
Thanks for the pointer. I sure need to read up on SQL!
Regards
/T