siplus

Fixing empty values in the result of a eSQL: an idea

Discussion created by siplus on Apr 15, 2015
Latest reply on Apr 17, 2015 by justinc

Hi all,


we do a lot of graphic representation of measurements data extracted via ExecuteSQL from Filemaker tables.

We use the very powerful xmChart plugin for plotting the data.

 

The problem is that the lists we calculate have often segments of missing data, like 20 in a row.

 

Otoh, xmChart expects a value for each list element, and a ¶ will simply cause the list to shrink, causing a misalign of the y data.

 

What it wants (and will accept) is NULL as a placeholder for no data.

 

Example:

 

20                                          20                                                  20

30                                          30                                                  30

                                              15                                                  NULL

                       --->                 17           but wanted is                  NULL

15                                                                                                15

                                                                                                    NULL

17                                                                                                17

 

 

Of course the received list can be post processed, with loops or recursive custom functions, but what I wanted was to get the data already ok.

 

I also did not want to create a calc field in the original data and pull that via SQL as the data files can have milions of records and adding a calc would have been a byte waste.

 

I think that others might have similar problems (maybe want a 0 instead of my NULL) so here is the way I solved it:

 

Instead of SELECT myValue FROM ...

 

I use

 

SELECT

 

 

STRVAL(myValue) ||

   CASE

       WHEN

       STRVAL(myValue) IS NULL

       THEN 'NULL'

        ELSE ''

   END

 

 

FROM ...

 

 

 

 

hope this will help somebody.

 

Attached: using 0 as default for empty (instead of NULL), in filemaker graph.

Attachments

Outcomes