Join two different sized Tables by a tag and not by time

Grafana InfluxDB
1

Hello,
im new to Flux and to the community so please tell me if i posted the topic in the wrong category or with the wrong tags. I couldn’t do this task using the Transform tabs so i hoped i could do this with flux

I have two data streams from a factory. The first one contains factory data e.g. temperatures or weights.

time field_float_value_float tag_generic_name tag_workorder
24.2.21 4:56:50 13 Weight1 A001
24.2.21 4:58:50 380 Weight1 A002
24.2.21 5:03:50 14 Weight1 A001
24.2.21 5:05:50 386 Weight1 A002
24.2.21 5:24:50 14 Weight1 A001
24.2.21 5:26:50 379 Weight1 A002
24.2.21 5:36:50 15 Weight1 A001
24.2.21 5:38:50 381 Weight1 A002
24.2.21 5:53:50 13 Weight1 A001
24.2.21 5:54:50 386 Weight1 A002
24.2.21 5:01:50 359 Weight2 A001
24.2.21 5:04:50 69 Weight2 A002
24.2.21 5:23:50 358 Weight2 A001
24.2.21 5:26:50 65 Weight2 A002
24.2.21 5:37:50 359 Weight2 A001
24.2.21 5:40:50 64 Weight2 A002
24.2.21 5:52:50 359 Weight2 A001
24.2.21 5:55:50 64 Weight2 A002
24.2.21 6:07:50 360 Weight2 A001
24.2.21 6:10:50 66 Weight2 A002

The second table contains data about the order e.g. the ordered amount, product type etc.

time field_integer_bestellmenge_value_int field_float_verlademenge_value_float tag_generic_name tag_workorder tag_rezeptnummer_version tag_artikelnummer
23.2.21 8:30:00 6000 5900 Auftragsdaten A001 17342642_2 Art001
23.2.21 9:30:00 6000 6100 Auftragsdaten A002 2223600758_1 Art002
24.2.21 5:00:09 18000 18030 Auftragsdaten A003 17342642_2 Art003
25.2.21 7:10:09 12000 10890 Auftragsdaten A004 2223600758_1 Art004

I tried to join both tables by the “workorder” column but the result is always “no data”
The Query i tried is

tab1 = from(bucket: “zone/autogen”)
|> range(start: -1000h)
|> filter(fn: (r) =>
r._measurement == “Flux_test”
)
|> pivot(
rowKey:[“_time”],
columnKey: [“generic_name”,“_field”],
valueColumn: “_value”
)
tab2 = from(bucket: “zone/autogen”)
|> range(start: -720h)
|> filter(fn: (r) =>
r._measurement == “Schokinag_Testfabrik_Auftragsdaten”
)
|> pivot(
rowKey:[“_time”],
columnKey: [“generic_name”,“_field”],
valueColumn: “_value”
)
|> drop(columns:[“_time”])
join(
tables:{A:tab1,B:tab2},
on:[“workorder”],
)

What i expected is a new table that looks like the following table

image
image1422×412 245 KB

Can anyone please help me with this?
I’m using InfluxDB 2.0 and trying to visualize the tables in Grafana 7.4

2

I have an Update: If i drop the “_time” column the join works perfectly. What i try to do is to drop the “_time” column on the Order table but keep it on the other. In this case the join on the “workorder” column doesn’t work. Maybe somone can help me find the right function.