I am trying this syntax:both the tables return rows but not the join output through there are common values.
import “join”
// Step 1: Query the mapping data
mappingData = from(bucket: “test1”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “mapping”)
|> filter(fn: (r) => r.machine_id == “${machine_id}” and r.site_name == “${site_name}”)
// Step 2: Filter and pivot the mapping data to get a single row
pivotedMappingData = mappingData
|> filter(fn: (r) => r._field == “camera_car_id” or r._field == “camera_office_id”)
|> pivot(rowKey: [“machine_id”, “site_name”], columnKey: [“_field”], valueColumn: “_value”) // Note the array for columnKey
// Debug output for transformed mapping data
pivotedMappingData |> yield(name: “pivoted_mapping_data_output”)
// Step 3: Query the load time data
loadTimeData = from(bucket: “test2”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “data”)
|> filter(fn: (r) => r._field == “data_status”)
|> filter(fn: (r) => r._value == 0 or r._value == 1)
// Debug output for load time data
loadTimeData |> yield(name: “load_time_data_output”)
// Step 4: Join the load time data with the transformed mapping data
joinedData = join.inner(
left: loadTimeData,
right: pivotedMappingData,
on: (l, r) => l.car_id == r.camera_car_id and l.office_id == r.camera_office_id,
as: (l, r) => ({l with camera_car_id: r.camera_car_id, camera_office_id: r.camera_office_id})
)
// Output the joined data
joinedData |> yield(name: “joined_data_output”)
