Fill No Data with 0 for calculations


I am trying to do simple addition using Transform : Add field from calculation. But when one of the field is having no data the calculation don’t work. To fix that I tried to fill it with 0, but it is not working. My query looks like this:
SELECT sum(diff) from ( select non_negative_difference(“value”) as diff FROM “counter” WHERE (“tag_name” = ‘tag1’) AND $timeFilter fill(0) )

Same case is with many other queries also, any way to replace no data with 0?

can you please follow these steps to share your raw unformatted data? That way, the community can try and mock up your problem :+1:

Raw Data looks something like this:

Time B
02-12-2021 10:59:49 1485
16-12-2021 19:15:15 1485
17-12-2021 18:36:09 1485
20-12-2021 21:52:13 1485
22-12-2021 13:51:15 1485
23-12-2021 20:31:57 1485
23-12-2021 22:20:14 1485
Time A
24-11-2021 16:37:24 111829
24-11-2021 16:37:25 111829
24-11-2021 16:37:27 111829
24-11-2021 16:37:29 111829
24-11-2021 16:37:30 111829
24-11-2021 16:37:32 111829
24-11-2021 16:37:33 111829
24-11-2021 16:37:35 111829
24-11-2021 16:37:37 111829
24-11-2021 16:37:38 111829
24-11-2021 16:37:40 111829
24-11-2021 16:37:41 111829
24-11-2021 16:37:43 111829
24-11-2021 16:37:45 111829
24-11-2021 16:37:46 111829
24-11-2021 16:37:48 111829
24-11-2021 16:37:49 111829
24-11-2021 16:37:51 111829
24-11-2021 16:37:53 111829
24-11-2021 16:37:54 111829
24-11-2021 16:37:57 111829
24-11-2021 16:37:59 111829
24-11-2021 16:38:01 111829
24-11-2021 16:38:02 111829
24-11-2021 16:38:04 111829
24-11-2021 16:38:05 111829
24-11-2021 16:38:07 111829
24-11-2021 16:38:10 111829
24-11-2021 16:38:13 111830
24-11-2021 16:38:15 111830
24-11-2021 16:38:17 111830
24-11-2021 16:38:18 111830
24-11-2021 16:38:20 111830
24-11-2021 16:38:21 111830
24-11-2021 16:38:23 111830
24-11-2021 16:38:25 111830
24-11-2021 16:38:26 111830
24-11-2021 16:38:28 111830
24-11-2021 16:38:29 111830
24-11-2021 16:38:31 111830
24-11-2021 16:38:33 111830
24-11-2021 16:38:34 111830
24-11-2021 16:38:36 111830
24-11-2021 16:38:37 111830
24-11-2021 16:38:39 111830
24-11-2021 16:38:41 111830
24-11-2021 16:38:42 111830
24-11-2021 16:38:44 111830
24-11-2021 16:38:45 111830
24-11-2021 16:38:47 111830
24-11-2021 16:38:49 111830
24-11-2021 16:38:50 111830
24-11-2021 16:38:52 111830
24-11-2021 16:38:53 111830
24-11-2021 16:38:55 111830
24-11-2021 16:38:57 111830

Hi @mattabrams , any solution to this ?

1 Like

sorry @satbirsingh just getting back from the holiday break. Will try and mock this up this week. more soon :+1:

@satbirsingh can you check out this dashboard? Please let me know if you can’t access it.

https://play.grafana.org/d/s8ep4PAnz/mja-add-field-transformation?orgId=1

I mocked your data and think I have what you want. Using the reduce method inside the add field from calculation seems to handle empty values.

1 Like

@mattabrams, thanks for the solution. This works good when adding two fields, but not when we are subtracting two. I have subtractions and divisions as well in my calculations. Difference using reduce row leads to zero value when null values are present.

1 Like

ok I think I got this working. Check out the updated dashboard. If you select the special value mapping option, then you can value map things like null, NaN, and undefined. Using that and then explicitly setting the fields as numbers not strings, and voila:

I added a constant value to the database that stores “1” and request it along with the rest of the data through an additional SELECT, this prevents the absence of data, sorry for my english