08. Join: sales with a price reference

Join prices to sales by a numeric key while retaining rows with no price.

Task and prerequisites

Join prices to sales by a numeric key while retaining rows with no price.

This example uses demonstration data and settings. Validate the expected result in your own environment before connecting production destinations. Identifiers demo_* are illustrative: select your own training connections or sources in the editor. The JSON below shows the block and connection structure, not a ready-to-transfer flow archive.

Training input data

sales.csv

ID;Qty
1;2
2.0;5
4;1

prices.csv

ID;Price
1.0;5
2;3
3;4

Blocks, connections and exact settings

08. Join: sales with a price reference. Example 08 topology: 4 blocks, 3 connections. Node identifiers and ports from the configured graph are preserved. — **Flow diagram 08**
Example 08 topology: 4 blocks, 3 connections. Node identifiers and ports from the configured graph are preserved.

Add the listed blocks in the designer, open each block's properties and set these values. Names demo_* identify training sources and connections.

Block	Setting
1 File `sales`	Role: `source` Source: `demo_join_sales` Read settings: Sheet: `CSV` Range: `A1:B4` First row is the header: `так` Delimiter: `;`
2 File `prices`	Role: `source` Source: `demo_join_prices` Read settings: Sheet: `CSV` Range: `A1:B4` First row is the header: `так` Delimiter: `;`
3 Join `join`	Inputs: Identifier: `sales` alias: `sales` Identifier: `prices` alias: `prices` Join type: `left` Cardinality: `many_to_one` Keys: Left field: `sales.ID` Right field: `ID` Numeric comparison: `так` Null keys are equal: `ні` Decimal separator: `.`
4 Data export `out`	Format: `json` File name: `sales_with_prices` Purpose: `download` Delimiter: `;` Time zone: `Europe/Kyiv`

Port connections

sales out → join sales
prices out → join prices
join out → out in

Complete graph configuration as JSON

{
  "version": 3,
  "nodes": [
    {
      "id": "sales",
      "type": "file",
      "label": "sales",
      "config": {
        "role": "source",
        "source_id": "demo_join_sales",
        "options": {
          "sheet": "CSV",
          "range": "A1:B4",
          "header": true,
          "delimiter": ";"
        }
      }
    },
    {
      "id": "prices",
      "type": "file",
      "label": "prices",
      "config": {
        "role": "source",
        "source_id": "demo_join_prices",
        "options": {
          "sheet": "CSV",
          "range": "A1:B4",
          "header": true,
          "delimiter": ";"
        }
      }
    },
    {
      "id": "join",
      "type": "join",
      "label": "join",
      "config": {
        "inputs": [
          {
            "id": "sales",
            "alias": "sales"
          },
          {
            "id": "prices",
            "alias": "prices",
            "how": "left",
            "cardinality": "many_to_one",
            "keys": [
              {
                "left": "sales.ID",
                "right": "ID",
                "numeric": true
              }
            ]
          }
        ],
        "nulls_equal": false,
        "decimal": "."
      }
    },
    {
      "id": "out",
      "type": "export",
      "label": "out",
      "config": {
        "format": "json",
        "filename": "sales_with_prices",
        "destination": "download",
        "delimiter": ";",
        "timezone": "Europe/Kyiv"
      }
    }
  ],
  "edges": [
    {
      "source": "sales",
      "target": "join",
      "source_port": "out",
      "target_port": "sales"
    },
    {
      "source": "prices",
      "target": "join",
      "source_port": "out",
      "target_port": "prices"
    },
    {
      "source": "join",
      "target": "out",
      "source_port": "out",
      "target_port": "in"
    }
  ]
}

Expected result

sales.ID	sales.Qty	prices.ID	prices.Price
1	2	1.0	5
2.0	5	2	3
4	1	NULL	NULL

Numeric comparison treats 1 and 1.0 as equal. LEFT JOIN retains ID=4 with an empty price.

Run and verify

Add both sources and connect them to separate named Join inputs.
Set sales as the first input without keys; define keys for the second input, prices.
Run and check 3 rows; ID=4 must have NULL, not an accidentally substituted price.

Common errors

The left key must include its alias: sales.ID, not just ID.
Two prices for one ID violate many_to_one and must be corrected in the reference data.
With numeric disabled, the text values "2.0" and "2" are not equal.