Function Names
smart num, smart if numDescription
These two functions are an upgrade variant of clean num() and clean if num().
The functions smart num and smart if num provide automatic up- and downscaling feature if (e.g. metric) scaling units are specified.
As long no user specific units and scaling factors are specified in the 3rd and 4th parameters, the following units will be recognized and rescale
the numbers. These stanard units are not case sensitive.
General Scaling Factors:
| Units | Scaling Factor | Explanation |
|---|---|---|
| ¢, pct, % | 0.01 | Cent, percent |
| ‰ (‰) | 0.001 | Per mille, part per thousand, see 1 |
| ‱ (&ertenk;), bp | 0.000 1 | Part per ten thousand, base point (in finance), see 1 |
| pcm | 0.000 01 | Percent mille, part per hundred thousand |
| ppm | 0.000 001 | Part per million |
| ppb | 0.000 000 001 | Part per billion |
| k, t, tsd | 1,000 | Kilo, thousand |
| m, mn, mln, mio | 1,000,000 | Mega, million |
| g, b, bn, bln | 1,000,000,000 | Giga, billion (Note: B4P uses the U.S. English interpretation of billion) |
| mrd, md, mia | 1,000,000,000 | Milliard (common in Great Britain and other languages) |
Indian Scaling Factors:
| Units | Scaling Factor | Explanation |
|---|---|---|
| lakh, lk, lac | 100,000 | Indian hundred thousand |
| crore, cr | 10,000,000 | Indian ten milllion |
| arab | 1,000,000,000 | Indian 1 billion (British English: Indian 1 milliard) |
Prefix letters before 3-letter currency codes, not case sensitive;
| Txxx, Kxxx | 1,000 | TGBP, KUSD, KEUR, etc. |
|---|---|---|
| Mxxx | 1,000,000 | MUSD, MCHF |
| Bxxx | 1,000,000 | BUSD, BRUB |
The different prefixes can be combined, e.g. smart num ( 1 kEUR % ) returns 10.
The function smart if num behaves similarly like clean if num: Contents without numbers inside are passed through as strings.
Application hint: After loading a table from a different source, apply this function on all applicable number columns in order to clean
them up and do necessary scalings.
Vectorization: This function supports vectorization in the 1st function parameter.
Instead of providing a single value, you can provide a set or even a nested set which contain multiple values.
The function will then process every value and its return value contains a corresponding set containing all results.
Call as: function
Restrictions
Indirect parameter passing is disabled
Vectorization is allowed in the 1st function parameter
Parameter count
1, 2, 4
Parameters
| No. | Type | Description |
|---|---|---|
| 1 input |
numeral or string set |
Value If string and containing digits: Conversion to numeral. |
| 2 input |
string | Decimal symbol Use single character symbol, e.g. '.' or ','. |
| Opt. 3 input |
set containing strings | Custom units Specify at least 1 unit name. The units provided will be used instead (and not in addition) of the standard units.
Attention:Comparison of these units is case-sensitive. This allows to distinguish lower-case letters like "m" for milli and upper-case leeters like "M" for mega.
|
| Opt. 4 input |
set containing numerals | Custom scaling factors For each custom unit defined in the previous function parameter, the correponding scaling factor needs to be added. |
Return value
| Type | Description |
|---|---|
| numeral string set |
number Contains resulting number. Strings may be returned by the function smart if num if the input value contains no digits. Paramter set containing results is returned if vectorization has been applied. |
Examples
echo( smart num( "5.0 MUSD" )); // 5,000,000
echo( smart num( "EUR 1.2mn" )); // 1,200,000
echo( smart num( "INR 0.5 lk" )); // 50,000
echo( smart num( '50%' )); // 0.5
echo( smart num( 'Alc. 0.5 ‰' )); // 0.0005
echo( smart num( "1.23cr" )); // 12,300,000
echo( smart num( 1.23m, ".", { m, M }, {0.000001, 1000000} ));
echo( smart num( {"5.0 MUSD","INR 0.5 lk"} )); // Vectorization exampleOutput
5000000
1200000
50000
0.5
0.00005
12300000
0.00000123
{5000000,50000}