Translating codes — Mapping tables

A Link matches a source column to a dimension by item code. That works when both sides agree on the coding — and they usually don't. Your accounting system exports account 620100; your P&L has a line called OPEX. Nothing in the Link can bridge that gap, so every row lands nowhere.

A Mapping table is the bridge: a saved list of rules, each one saying this source code becomes that destination item. You build it once in the Data section and point as many Links at it as you like.

Two source codes can map to the same destination — that is how a detailed chart of accounts collapses into a reporting line. Rows landing on the same cell sum, exactly as they would without a mapping.

The sign

Each rule also decides what happens to the value:

• = passes it through unchanged;
• − inverts it. This is the workhorse for accounting sources, which commonly deliver revenue and liabilities as credits — negative numbers you want positive in a report;
• abs takes the magnitude, for sources whose polarity is inconsistent.

How a code is matched

• Exact codes win. 620100 is looked up first, before any pattern.
• A trailing * is a prefix pattern. 70* matches 700000, 701200, and so on. The * only works at the end, and only on the source side — a pattern in the destination column matches nothing.
• Among patterns, the first listed wins. If 6* sits above 62*, the broader rule catches everything and 62* never fires. List your specific patterns before your catch-alls.
• Blank cells need their own rule. Write <blank> on the source side to say where empty or missing source values go. No pattern, not even *, matches a blank — a blank code is usually a subtotal or header row you want to see, not fold into a catch-all. The same token works without a mapping table: a column matched directly to a dimension lands its blank cells on the item coded <blank>, if the dimension has one.
• Case never matters, on either side. 620100 matches whatever the source sends, and opex finds an OPEX item on the destination dimension. What the destination code still has to be is a real item code on that dimension — a destination that names no item skips those rows, and the run reports them as unmatched.

When a code isn't matched

Nothing goes wrong loudly. A source value with no matching rule is skipped — its row simply doesn't arrive, and the cube shows a smaller number than the source. A destination code that doesn't exist behaves the same way.

A blank source cell with no <blank> rule (or, on a directly matched column, no <blank> item) is skipped too, and the report names it as <blank> so you can tell it apart from a mistyped code. Every list of source values in the product uses that same word for a blank cell.

So the habit that matters: Preview the Link before you run it. The preview reports how many source rows would be skipped, and a surprising skip count is how a missing or mistyped rule announces itself. Once the values are in the cube, the only clue is a total that looks slightly wrong.

A run normally carries that warning forward too: an amber unmatched report naming the codes no rule covered. Where the gap is deliberate — a source whose scope is wider than this cube, so most of its codes are meant to fall away — the Mapping step's Missing items: Ignore switch drops that report, and the run stops nagging. It changes nothing about the transfer: those rows are still skipped, and the skipped count still shows. Only the per-code detail goes away, and only on runs — the editor's preview keeps the full diagnostic on purpose, since that is where you decide whether ignoring them is right.

Using one

Mapping tables live in the Data section, alongside your files and tables. Create one, set its source and destination dimensions (optional, but worth doing — XCubes then warns you about destination codes that don't exist), and fill in the rules. Auto-map can propose matches from the item codes and descriptions on both sides, which you then review.

The source side does not have to be a dimension. Point it at a column of a data table instead — the table, the sheet, then the column — and the editor lists that column's distinct values with the number of rows behind each. That is usually the better source: it is what the file actually contains, rather than the codes you hope it contains, so a value with no rule is visible as an unmapped entry instead of turning up later as a skipped row.

In the Link editor, each source column can either match codes directly or go through a mapping table — you pick the table per column. One crosswalk can serve every Link that reads the same source system.

See Populating cubes — Links & integrations for the transfer itself, and Dimensions for where the destination item codes come from. For the end-to-end method — reconciling a ledger export into statements that tie line-by-line on the first run — see Build financial statements from a ledger export.