Filter multiple ranges from one table, the ranges are saved in another table

Hello, I need to filter a field (table) according to the ranges that I have saved in a second table. Can anyone help me?

I have a field called ‘stoppages’, in which I save all machine stops, I have the following columns:

  • Duration_secs
  • Machine_name
  • Start_Datetime
  • Stop_datetime

Where start_datetime and stop_datetime are the datetimes that the stops started and ended, respectively.
After filtering the stoppages table according to my requirements I have the following result:

Now, I need to use the start and stop_datetime values to filter my speed table. I want to get all the speed data according to the range I chose and exclude the data from when the machine was stopped (which I obtained from the stoppages table).

The data from the screenshot above is saved in the data variable. My speed table has two columns: _time and _value.

I created a custom function, called filterData, to filter the data according to timestamps:

filterData = (table=<-) => {
    table1 = map(tables: data, fn: (r) => {
      firstRTime = table
        |> first()
        |> findColumn(fn: (key) => true, column: "_time")
      beforeTheStart = table
        |> range(start: firstRTime[0], stop: r.start_datetime)
      afterTheStop = table
        |> range(start: r.stop_datetime)
      return union(tables: [beforeTheStart, afterTheStop])
        |> group(columns: ["machine_name_generic"])
        |> keep(columns: ["_time", "_value", "_field"])
    return table1
  |> filterData()
  |> yield(name: "finalData")

In this function, I use the map function with the data table and try to filter my speed table. And so I have the following errors:

runtime error: map: map function must return an object, got stream
invalid: error: stream[A] is not Record (argument tables)

To summarize, in the function I tried to filter the data in the speed table according to each row in the data table, but it did not work.

Can anyone tell me how to do this, please?

Hi @alice.oliveira,

Thanks for your question. I’m going to ask around to see if I can find an answer and get back to you.

1 Like

Oi @DanCamp,

Ok, thanks!