# Efficient way to join several time series commands when some of them return null values - is there any other way?

**URL:** <https://community.dynatrace.com/t/efficient-way-to-join-several-time-series-commands-when-some-of-them-return-null-values-is-there-any-other-way/54584>\
**Category:** DQL\
**Tags:** dql, dynatrace-managed, grail, logs, timeseries\
**Created:** [January 11, 2026, 8:29am UTC](https://community.dynatrace.com/t/efficient-way-to-join-several-time-series-commands-when-some-of-them-return-null-values-is-there-any-other-way/54584 "2026-01-11T08:29:33Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gilgi](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/gilgi/32/11747_2.png) [@gilgi](https://community.dynatrace.com/u/gilgi)\
**Post date:** [January 11, 2026, 8:29am UTC](https://community.dynatrace.com/t/efficient-way-to-join-several-time-series-commands-when-some-of-them-return-null-values-is-there-any-other-way/54584/1 "2026-01-11T08:29:33Z")

</div>

Hi,

We have a situation in which we want to calculate (from logs) the percentage of errors from the total number of requests based on officename and apinames. It could happen that some of the offices/api’s will not experience any errors during the timeframe, hence its timeseries command will return null. Doing a math operation on null values will return nulls and as we want to calculate the percentages over time, we need to be able to “create” an array, equal in size to the number of samples (based on timeframe and interval), of zeros in such case. As I don’t know of any function (coalesce will return a single zero and not an array) to do this I’ve done the following:

```markup
timeseries {
    total = sum(apigee.apiname_requestcount, default:0)//, scalar: true )
  },
  by: { destinationoffice, apiname },
  filter: { 
    not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and
    not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*")
  }
| fields interval=record(
          timeframe.start = timeframe[start] + interval * iIndex(),
          totalrequests=coalesce(total[],0),
          destinationoffice,
          apiname
          )
| expand interval
| fieldsFlatten interval
| fieldsRemove interval
// Add 4xx errors
| join [
  timeseries {
    errors = sum(apigee.apiname_requestcount, default:0)//, scalar: true )
  },
  by: { destinationoffice, apiname },
  filter: { 
    not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and
    not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") and
    responsecode >= 400 and responsecode <=499
  }
| fields interval1=record(
          timeframe.start = timeframe[start] + interval * iIndex(),
          errors=coalesce(errors[],0),
          destinationoffice,
          apiname
          )
| expand interval1
| fieldsFlatten interval1
| fieldsRemove interval1
], on:{
  left[interval.timeframe.start]==right[interval1.timeframe.start], 
  left[interval.destinationoffice]==right[interval1.destinationoffice], 
  left[interval.apiname]==right[interval1.apiname] 
}, prefix:"error4xx.", kind:leftOuter
| fieldsAdd errors4xx = coalesce(error4xx.interval1.errors,0)
//add errors 5xx
| join [
  timeseries {
    errors = sum(apigee.apiname_requestcount, default:0)//, scalar: true )
  },
  by: { destinationoffice, apiname },
  filter: { 
    not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and
    not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*") and
    responsecode >= 500 and responsecode <=599
  }
| fields interval1=record(
          timeframe.start = timeframe[start] + interval * iIndex(),
          errors=coalesce(errors[],0),
          destinationoffice,
          apiname
          )
| expand interval1
| fieldsFlatten interval1
| fieldsRemove interval1
], on:{
  left[interval.timeframe.start]==right[interval1.timeframe.start], 
  left[interval.destinationoffice]==right[interval1.destinationoffice], 
  left[interval.apiname]==right[interval1.apiname] 
}, prefix:"error5xx.", kind:leftOuter
| fieldsAdd errors5xx = coalesce(error5xx.interval1.errors,0)
| fieldsAdd errorPct = coalesce((errors5xx+errors4xx)*100 / interval.totalrequests,0)
// | summarize {errors=collectArray(errorPct), start=min(interval.timeframe.start), end=max(interval.timeframe.start) },by: {destinationoffice = interval.destinationoffice, apiname = interval.apiname}
| makeTimeseries errorPct=avg(errorPct), by: {destinationoffice = interval.destinationoffice, apiname = interval.apiname}, time: interval.timeframe.start, interval:1m

```

Which is actually is based on the great tip from&nbsp;[@marco\_irmer](https://community.dynatrace.com/user/19769) found on&nbsp;[https://community.dynatrace.com/t5/Dynatrace-tips/Pro-Tip-DQL-Query-Snippets-Collection/m-p/255109](https://community.dynatrace.com/post/255109)&nbsp;. What I’ve done is seperate the arrays into single value lines and used the coalesce to do the join and then in the end recreate the timeseries.

To all of you - DQL experts - any better way to do this?

---

<div class="post-metadata">

**Author:** ![Julius\_Loman](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/julius_loman/32/51_2.png) [@Julius\_Loman](https://community.dynatrace.com/u/Julius_Loman)\
**Post date:** [January 11, 2026, 8:16pm UTC](https://community.dynatrace.com/t/efficient-way-to-join-several-time-series-commands-when-some-of-them-return-null-values-is-there-any-other-way/54584/2 "2026-01-11T20:16:24Z")

</div>

[@gilgi](https://community.dynatrace.com/user/12008),&nbsp;your query looks quite overengineered&nbsp;:dynaspin:

From what I see and deduce (maybe I missed something), it’s doable in a single timeseries command, something like this:

```markup
timeseries { 
  total=sum(apigee.apiname_requestcount, default: 0)
  , errors_4xx=sum(apigee.apiname_requestcount, default:0, filter:{ responsecode >= 400 and responsecode <500 })
  , errors_5xx=sum(apigee.apiname_requestcount, default:0, filter:{ http.response.status_code>=500 and http.response.status_code<600})
}
, union:{ true }
, by: { destinationoffice, apiname }
, filter: { 
    not matchesValue(destinationoffice, "*-t.*") and not matchesValue(destinationoffice, "*-d.*") and
    not matchesValue(destinationoffice, "*.dev.*") and not matchesValue(destinationoffice, "*test*")
  }

```

The key is:

- **filter** can be applied to individual metric keys independently
- **union** &nbsp;true is required if some of the datapoints can be absent, see [docs](https://docs.dynatrace.com/docs/discover-dynatrace/platform/grail/dynatrace-query-language/commands/metric-commands#join-behavior-union-parameter)

Hope this helps. A [sample](https://wkf10640.apps.dynatrace.com/ui/intent/dynatrace.notebooks/view-query#%7B%22visualization%22%3A%22lineChart%22%2C%22visualizationSettings%22%3A%7B%22autoSelectVisualization%22%3Atrue%7D%2C%22dt.timeframe%22%3A%7B%22from%22%3A%22now()-24h%22%2C%22to%22%3A%22now()%22%7D%2C%22dt.query%22%3A%22timeseries%20%7B%20%5Cn%20%20total%3Dsum(dt.service.request.count%2C%20default%3A%200)%5Cn%20%20%2C%20errors_4xx%3Dsum(dt.service.request.count%2C%20default%3A0%2C%20filter%3A%7B%20http.response.status_code%3E%3D400%20and%20http.response.status_code%3C500%7D)%5Cn%20%20%2C%20errors_5xx%3Dsum(dt.service.request.count%2C%20default%3A0%2C%20filter%3A%7B%20http.response.status_code%3E%3D500%20and%20http.response.status_code%3C600%7D)%5Cn%7D%2C%20by%3A%7Bdt.entity.service%7D%2C%20filter%3A%7B%20dt.entity.service%20%3D%3D%20%5C%22SERVICE-EB9383023479296B%5C%22%20%7D%2C%20union%3A%7B%20true%20%7D%5Cn%7C%20fieldsAdd%20errorRate%3Derrors_4xx%5B%5D%2Berrors_5xx%5B%5D%2Ftotal%5B%5D%22%2C%22hideInput%22%3Afalse%2C%22sourceApplication%22%3A%22dynatrace.notebooks%22%2C%22querySettings%22%3A%7B%22maxResultRecords%22%3A1000%2C%22defaultScanLimitGbytes%22%3A500%2C%22maxResultMegaBytes%22%3A1%2C%22defaultSamplingRatio%22%3A10%2C%22enableSampling%22%3Afalse%7D%7D)&nbsp;using the built-in service metric in the playground).

---

<div class="post-metadata">

**Author:** ![gilgi](https://dub1.discourse-cdn.com/dynatrace/user_avatar/community.dynatrace.com/gilgi/32/11747_2.png) [@gilgi](https://community.dynatrace.com/u/gilgi)\
**Post date:** [January 12, 2026, 6:57am UTC](https://community.dynatrace.com/t/efficient-way-to-join-several-time-series-commands-when-some-of-them-return-null-values-is-there-any-other-way/54584/3 "2026-01-12T06:57:43Z")

</div>

[@Julius\_Loman](https://community.dynatrace.com/user/3364)&nbsp;and this is why it says you’re a legend!! thanks.