Price + Currency Split¶
What
Split a single mixed-notation Price column — $12.99, 50.00 EUR,
€3.50, 1,234.00 USD — into a clean numeric price and a separate
currency code, using bxp's PRICE_VALUE / PRICE_CURRENCY builtins.
Synthetic / teaching example
The data here is constructed, not sourced
— sample.csv is hand-written rows engineered to cover the common currency
notations (leading symbol, trailing ISO code, comma thousands, empty). The
problem class is real; the rows are not. (Mixed-currency price columns live in
private marketplace/ERP exports with no public dataset.)
Why interesting¶
Prices arrive glued to their currency in a dozen
incompatible shapes — symbol-before ($, €), code-after (EUR, CZK), with
or without thousands separators. To SUM, convert, or compare them you first
need the bare number and the currency in their own columns. That is normally a
pile of regexes; bxp has a dedicated pair of functions for it.
Problem class documented in. (sources for the problem class — not for the data)
- ISO 4217 currency codes —
the same amount ships as
$,€,EUR,USD, … across systems. - Generic data-cleaning guides repeatedly cover separating currency symbols from numeric values before analysis.
The trick¶
(see inline comments in sample.json):
Every expression below is clickable: it runs in your browser — bxp's own
evaluator, compiled to WebAssembly — against the sample.csv further down. The
panel shows the first row; show all runs it over every row, which is the
fastest way to see why each piece is there.
PRICE_CURRENCY([Price])→ the currency code ($12.99→USD,50.00 EUR→EUR,€3.50→EUR).PRICE_VALUE([Price])→ the numeric part with the symbol/code removed. It leaves the comma thousands in place (1,234.00), so wrap it:REPLACE(PRICE_VALUE([Price]), ',', '') * 1lands a clean number.- An
IF(ISEMPTY([Price]), '', REPLACE(PRICE_VALUE([Price]), ',', '') * 1)guard keeps a genuinely empty price empty rather than coercing it to0. The guard must beISEMPTY, not[Price] = '': that comparison coerces, so a price of0would test as empty and a real zero would vanish.
Final result¶
One ragged column becomes two clean ones:
price is now a real number and currency a separate code — ready for FX
conversion or a GROUP BY currency total.
Worth trying in the panel
PRICE_VALUE([Price])on show all — row 4 keeps its comma (1,234.00), which is the whole reasonREPLACEis there.REPLACE(PRICE_VALUE([Price]), ',', '') * 1without theIFguard — the empty price coerces to0instead of staying empty.UPPER([Item])orLEN([Item])— any expression works, not just the ones this example uses.
Sample data¶
Run it with bxp-cli --config ./sample.json --template price_currency_split:
{
// Teaching example — synthetic data. A product/marketplace export where one
// `Price` column mixes currency notations: leading symbol ($12.99, €3.50),
// trailing ISO code (50.00 EUR, 24.00 CZK), and comma thousands
// (1,234.00 USD). Downstream you almost always need the NUMBER and the
// CURRENCY in separate columns. bxp ships two builtins for exactly this:
// PRICE_VALUE (strip the symbol/code) and PRICE_CURRENCY (extract it).
conversion_templates: {
price_currency_split: {
data_dir: ".",
file_pattern_in: ".csv",
file_pattern_out: ".csvx",
csv_text_quote_in: "double",
input_schema: {
$item: "[Item]",
// TRICK 1 — split value from currency:
// PRICE_VALUE("$12.99") → "12.99"
// PRICE_CURRENCY("$12.99") → "USD"
// PRICE_VALUE keeps the comma thousands as-is ("1,234.00"), so wrap it
// in REPLACE(…, ',', '') * 1 to land a clean number; the ISEMPTY guard keeps
// a genuinely empty price empty. It has to be ISEMPTY and not
// [Price] = '' — that test coerces, so a price of "0" would read as
// empty and the row would lose a real zero.
$price: "IF(ISEMPTY([Price]), '', REPLACE(PRICE_VALUE([Price]), ',', '') * 1)",
$currency: "PRICE_CURRENCY([Price])"
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
item: "$item",
price: "$price",
currency: "$currency"
}
}
}
}