# Flux query to compare data b/w 2 tables **URL:** \ **Category:** InfluxDB 2\ **Tags:** grafana, flux\ **Created:** [August 1, 2022, 9:19am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944 "2022-08-01T09:19:54Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Aritra666B](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@Aritra666B](https://community.influxdata.com/u/Aritra666B)\ **Post date:** [August 1, 2022, 9:19am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/1 "2022-08-01T09:19:55Z")
```auto MainTable ======================================================= |timestamp |IPAddress | ======================================================= |123456789 |192.168.5.2 | |123456788 |192.168.5.3 | |123456787 |192.168.5.4 | |123456786 |192.168.5.5 | ======================================================= ``` ```auto LiveStatusTable ======================================================= |timestamp |IPAddress |Value | ======================================================== |123456789 |192.168.5.2 |1 | |123456788 |192.168.5.3 |1 | ======================================================= Now I need to compare these tables to check which IPAddress are missing in the 2nd table using Flux. ``` ---
**Author:** ![Jay\_Clifford](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jay_clifford/32/8287_2.png) [@Jay\_Clifford](https://community.influxdata.com/u/Jay_Clifford)\ **Post date:** [August 1, 2022, 9:42am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/2 "2022-08-01T09:42:13Z")
Hi @Aritra666B, Could you use a join to achieve this: [Joins in Flux | Additional resources | InfluxData Documentation](https://docs.influxdata.com/resources/videos/joins-in-flux/) ---
**Author:** ![Aritra666B](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@Aritra666B](https://community.influxdata.com/u/Aritra666B)\ **Post date:** [August 1, 2022, 11:57am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/3 "2022-08-01T11:57:51Z")
Hi @Jay_Clifford , With Join I will get the common values. In my case I need to check which IP address are not in LiveStatusTable but are in the MainTable to determine whihc systems are offline. Is there a way for me to do this using Flux? ---
**Author:** ![Jay\_Clifford](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/jay_clifford/32/8287_2.png) [@Jay\_Clifford](https://community.influxdata.com/u/Jay_Clifford)\ **Post date:** [August 3, 2022, 12:07pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/4 "2022-08-03T12:07:02Z")
Hi @Aritra666B, I am wondering if you did an outer join between the two tables. This would then leave null values within rows where there is not a match. You could then use map() to conditional check if the two columns match one another. Could you export your a sample of your data and I will see if I can build the query for you ---
**Author:** ![Aritra666B](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@Aritra666B](https://community.influxdata.com/u/Aritra666B)\ **Post date:** [August 16, 2022, 4:29am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/5 "2022-08-16T04:29:09Z")
Hi @Jay_Clifford , sorry for late reply as I was sick & on leave. I have imported them in csv but how to attach them here? ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [August 16, 2022, 3:58pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/6 "2022-08-16T15:58:06Z")
You could just use [`testing.diff()`](https://docs.influxdata.com/flux/v0.x/stdlib/testing/diff/). You should make sure the schema of the two input tables are the same, so if you’re just trying to see what IP Addresses are missing, I’d just drop all columns except `IPAddress`. You’ll also need to make sure rows are sorted in the same order. Here’s a working example using the data you provided above: ```javascript import "array" import "testing" MainTable = array.from( rows: [ {timestamp: 123456789, IPAddress: "192.168.5.2"}, {timestamp: 123456788, IPAddress: "192.168.5.3"}, {timestamp: 123456787, IPAddress: "192.168.5.4"}, {timestamp: 123456786, IPAddress: "192.168.5.5"}, ], ) |> keep(columns: ["IPAddress"]) |> sort(columns: ["IPAddress"]) LiveStatusTable = array.from( rows: [ {timestamp: 123456789, IPAddress: "192.168.5.2", Value: 1}, {timestamp: 123456788, IPAddress: "192.168.5.3", Value: 1}, ], ) |> keep(columns: ["IPAddress"]) |> sort(columns: ["IPAddress"]) testing.diff(got: LiveStatusTable, want: MainTable) ``` This returns: | \_diff | IPAddress | | --- | --- | | - | 192.168.5.4 | | - | 192.168.5.5 | ---
**Author:** ![b\_pennies](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/b_pennies/32/12612_2.png) [@b\_pennies](https://community.influxdata.com/u/b_pennies)\ **Post date:** [August 17, 2022, 8:08pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/7 "2022-08-17T20:08:07Z")
@scott , @Jay_Clifford I’m trying to do the same thing, but am restricted to InfluxDB 1.8 + Flux 0.65.1 and therefore can’t use the “join” package. I’ve tried the solution using `testing.diff` and this works great as long as the “missing” values are contiguous in the “complete” table. If LiveStatusTable looked like this: ```auto LiveStatusTable = array.from( rows: [ {timestamp: 123456789, IPAddress: "192.168.5.2", Value: 1}, {timestamp: 123456788, IPAddress: "192.168.5.4", Value: 1}, ], ) ``` then the result would be incorrect. I’ve tried to solve this using the `getColumn` and/or `findColumn` to create an array of expected values and then use `filter` to leave only those rows in `LiveStatusTable` that do not appear in `MainTable`: ```auto expected_values = LiveStatusTable |> getColumn(column: "IPAddress") LiveStatusTable |> filter(fn: (r) => not contains(value: r["IPAddress"], set: expected_values) ``` But this always results in a `500 Internal Server Error: type error : missing object properties (schema) ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [August 17, 2022, 8:44pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/8 "2022-08-17T20:44:51Z")
@b_pennies The `missing object properties (schema)` error indicates you’re trying to use the `schema` package somewhere in your query. Is this your full query? The schema package wasn’t introduced until Flux 0.88.0, but it’s essentially a port of the `influxdata/influxdb/v1` package, so you should be able to use that instead with the version of Flux you’re using. ---
**Author:** ![b\_pennies](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/b_pennies/32/12612_2.png) [@b\_pennies](https://community.influxdata.com/u/b_pennies)\ **Post date:** [August 17, 2022, 9:27pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/9 "2022-08-17T21:27:29Z")
Here is the full query I’m running. ```auto import "strings" get_bucket = (datasource, kitchen) => if strings.containsStr(v: datasource, substr:"kitchen-data") then kitchen + "/autogen" else "kitchen/autogen" bucket = get_bucket(datasource: "kitchen-data (flux)", kitchen: "kitchen3") data = from(bucket: bucket) |> range(start: 2022-08-16T07:00:00Z, stop: 2022-08-17T06:59:59Z) |> filter(fn: (r) => r._field == "pizza_id") expected = data |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r.station == "dough-storage" and r._field == "pizza_id") |> distinct(column: "pizza_id") |> getColumn(column: "pizza_id") ``` I’m not sure where I’d be invoking the `schema` package ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [August 17, 2022, 10:56pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/10 "2022-08-17T22:56:08Z")
@b_pennies Ah, ok, I think I know why. In this particular case, you’re using `getColumn()` incorrectly. `getColumn()` actually requires `tableFind()` to extract a table from a stream of tables. `getColumn()` then extracts a column from the extracted table and returns an array of column values. So with your current query, getColumn is operating on the stream of tables passed to it from `distinct()`, not an extracted table. I’d actually suggest using `findColumn()` instead of `getColumn()`. This is a newer implementation that both extracts the table and returns an array of column values. It’s available in InfluxDB 1.8: ```javascript // ... expected = data |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r.station == "dough-storage" and r._field == "pizza_id") |> distinct(column: "pizza_id") |> findColumn(column: "pizza_id", fn: (key) => true) ``` Another problem here is that the InfluxDB query API requires that a query returns a stream of tables. Right now, this query doesn’t. You have variables defined, but they query doesn’t call any variable to return it’s value. You could just call `data` at the end of the query to return the `data` stream of tables. You wouldn’t be able to call `expected` because it returns an array. The InfluxDB API doesn’t support returning raw arrays. ---
**Author:** ![b\_pennies](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/b_pennies/32/12612_2.png) [@b\_pennies](https://community.influxdata.com/u/b_pennies)\ **Post date:** [August 18, 2022, 12:31am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/11 "2022-08-18T00:31:55Z")
@scott Ahh ok, that makes some sense now. I was able to get my query working thanks to your insights, as such: ```auto import "strings" $get_bucket bucket = get_bucket(datasource: ${datasource:json}, kitchen: ${kitchen:json}) data = from(bucket: bucket) |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r._field == "pizza_id") completed = data |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r.station == "serve-conveyor" and r._field == "pizza_id") |> distinct(column: "_value") |> tableFind(fn: (key) => true) |> getColumn(column: "_value") unfinished = data |> filter(fn: (r) => r.station == "dough-storage") |> aggregateWindow(every: ${window_interval}, fn: distinct) |> distinct() |> filter(fn: (r) => not contains(value: r._value, set: completed)) |> yield() ``` where I expect the `completed` array to be a list of pizza ids that is a superset of those found in the raw `unfinished` data. The final output is a table (or stream of tables with 1 table?) that only includes those ids that do not appear in the `unfinished` data. Interestingly I get a new error when trying to take your advice and using `findColumn()`. ```auto // ... completed = data |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r.station == "serve-conveyor" and r._field == "pizza_id") |> distinct(column: "_value") |> findColumn(column: "_value", fn: (key) => true) // ... ``` The error is simply `undefined identifier "findColumn"`. From the documentation I’m guessing that `findColumn` was introduced in v0.68.0 but I only have access up to v0.65.1, which would explain this error. ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [August 18, 2022, 2:52pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/12 "2022-08-18T14:52:26Z")
Yep, you’re right. `findColumn()` isn’t supported in 1.8 so you’ll have to stick with `tableFind() |> getColumn()`. Sorry about that. I’m glad the query is working as it should. Let me know if there’s anything else I can help with. ---
**Author:** ![b\_pennies](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/b_pennies/32/12612_2.png) [@b\_pennies](https://community.influxdata.com/u/b_pennies)\ **Post date:** [August 18, 2022, 7:01pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/13 "2022-08-18T19:01:49Z")
Thanks a ton for all the help @scott!!! ---
**Author:** ![Devendra\_Singh](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/devendra_singh/32/15709_2.png) [@Devendra\_Singh](https://community.influxdata.com/u/Devendra_Singh)\ **Post date:** [June 3, 2024, 10:19am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/14 "2024-06-03T10:19:32Z")
@scott I recently started learning flux and found myself in the same problem. I want to compare data between two tables. I want to find the number of active imie(which send data in the last 30 min) and the number of inactive imie that did not send any data in the last 40 min. all devices were active 24 hr ago. a device can send data multiple times in 5 min also imie is a tag. help me… ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [June 3, 2024, 4:13pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/15 "2024-06-03T16:13:26Z")
> [@Devendra\_Singh](#): > > I want to find the number of active imie(which send data in the last 30 min) and the number of inactive imie that did not send any data in the last 40 min. all devices were active 24 hr ago. > a device can send data multiple times in 5 min also imie is a tag. The first thing to understand is that InfluxDB can only provide data that does exist. I can’t tell you what data doesn’t exist (inactive imie). To figure this out, you have to query the list of all imie’s within the past 24 hours. To do this, you can just query all data from the last day, downsample the data to make it a little more manageable, ungroup it into a single table, and return all the unique values of `imie`: ```javascript imieAll = from(bucket: "example-bucket") |> range(start: -24h) |> last() |> group() |> unique(column: "imie") ``` You an do the same thing to find all the active `imie`s from the last 30 minutes and then count them: ```javascript imieActive = from(bucket: "example-bucket") |> range(start: -30m) |> last() |> group() |> unique(column: "imie") |> count() ``` To find the number of inactive `imie`s, you need to subtract the number of those active in the last 40 minutes from the total number of `imie`s: ```javascript imieTotal = (from(bucket: "example-bucket") |> range(start: -24h) |> last() |> group() |> unique(column: "imie") |> count() |> findColumn(fn: (key) => true, column: "_value"))[0] imieActive = from(bucket: "example-bucket") |> range(start: -30m) |> last() |> group() |> unique(column: "imie") |> count() imieActive |> map(fn: (r) => ({_value: imieTotal - r.imie})) ``` ---
**Author:** ![Devendra\_Singh](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/devendra_singh/32/15709_2.png) [@Devendra\_Singh](https://community.influxdata.com/u/Devendra_Singh)\ **Post date:** [June 5, 2024, 4:28am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/16 "2024-06-05T04:28:19Z")
@scott don’t know why getting this error `unique: schema collision detected: column "_value" is both of type float and string` when running the above query. I’m sending imie number as string but still getting this error. example imie: 12AC1243224SD12 ---
**Author:** ![Devendra\_Singh](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/devendra_singh/32/15709_2.png) [@Devendra\_Singh](https://community.influxdata.com/u/Devendra_Singh)\ **Post date:** [June 5, 2024, 4:57am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/17 "2024-06-05T04:57:23Z")
and sending this as a tag. ---
**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\ **Post date:** [June 5, 2024, 2:30pm UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/18 "2024-06-05T14:30:08Z")
Ok, that error is being caused by ungrouping the tables (`group()`). This combines all rows into a single table, but all values in a column must be of the same type. This means you have fields of different types. I think the best way around this would be to query specific a field by adding `filter()` after both range calls: ```javascript imieTotal = (from(bucket: "example-bucket") |> range(start: -24h) |> filter(fn: (r) => r._field == "example-field") |> last() |> group() |> unique(column: "imie") |> count() |> findColumn(fn: (key) => true, column: "_value"))[0] imieActive = from(bucket: "example-bucket") |> range(start: -30m) |> filter(fn: (r) => r._field == "example-field") |> last() |> group() |> unique(column: "imie") |> count() imieActive |> map(fn: (r) => ({_value: imieTotal - r.imie})) ``` ---
**Author:** ![Devendra\_Singh](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/devendra_singh/32/15709_2.png) [@Devendra\_Singh](https://community.influxdata.com/u/Devendra_Singh)\ **Post date:** [June 7, 2024, 6:32am UTC](https://community.influxdata.com/t/flux-query-to-compare-data-b-w-2-tables/25944/19 "2024-06-07T06:32:00Z")
Thanks for all the help @scott!!!