NUMBER macro
The NUMBER macro is available in HCL® Campaign and HCL Interact.
Syntax
NUMBER(data [, conversion_keyword])
Parameters
data
The
ASCII text data to convert to numerical values. This can be ASCII
text in quotes, a column of text, a cell range containing text, or
an expression evaluating to any of the above. For the format definition
of data
, see the "Macro Function Parameters"
section in the chapter in this guide for your HCL
product.
conversion_keyword
This optional keyword specifies how to interpret text formats for dates and times. Select one of the keywords in the following table.
1
.Conversion Keyword | Format | Description |
---|---|---|
0 |
##### |
Converts the first 5 characters of each text string into a unique number |
1 |
$ ( default) |
Converts dollar values to numerics (for example, "$123.45" to 123.45 ) |
2 |
% |
Converts a percentage value to numerics (for example, "50%" to 0.5 ) |
3 |
mm/dd/yy hh:mm |
Converts a date and time to the number of days elapsed since
January 1, 0000 (1900 is automatically added to the yy year) |
4 |
dd-mmm-yy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the yy year) |
5 |
mm/dd/yy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the yy year) |
6 |
mmm-yy |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (1900 is automatically
added to the yy year) |
7 |
dd-mmm |
Converts a date to the number of days since the beginning of
the year (for example, "01-FEB" to 32 ) |
8 |
mmm |
Converts a 3-letter month abbreviation to a value between 1-12
(for example, "DEC" to 12 ) |
9 |
{January | February | March … } |
Converts a fully spelled-out month name to a value between
1-12 (for example, "March" to 3 ) |
10 |
{Sun | Mon | Tue … } |
Converts a 3-day weekday abbreviation to a value between 0-6,
where Sunday marks the beginning of the week (for example, "Sun" to 0 ) |
11 |
{Sunday | Monday | Tuesday … } |
Converts a fully spelled-out weekday name to a value between
0-6, where Sunday marks the beginning of the week (for example, "Monday" to 1 ) |
12 |
hh:mm:ss {AM | PM} |
Converts the time to the number of seconds elapsed since 00:00:00
AM (midnight) (for example, "01:00:00 AM" to 3600 ) |
13 |
hh:mm:ss |
Converts the time to the number of seconds elapsed since 00:00:00
AM (midnight) (for example, "01:00:00" to 3600 ) |
14 |
hh:mm {AM | PM} |
Converts the time to the number of minutes elapsed since 00:00:00
AM (midnight) (for example, "01:00 AM" to 60 ) |
15 |
hh:mm |
Converts the time to the number of minutes elapsed since 00:00:00
AM (midnight) (for example, "01:00" to 60 ) |
16 |
mm:ss |
Converts the time to the number of seconds elapsed since 00:00:00
AM (midnight) (for example, "30:00" to 1800 ) |
17 |
ddmm |
Converts a date to the number of days since the beginning of
the year (for example, "3101" to 31 ) |
18 |
ddmmm |
Converts a date to the number of days since the beginning of
the year (for example, "31JAN" to 31 ) |
19 |
ddmmmyy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
20 |
ddmmmyyyy |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "31JAN0000" to 31 ) |
21 |
ddmmyy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
22 |
ddmmyyyy |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "31010000" to 31 ) |
23 |
mmdd |
Converts a date to the number of days since the beginning of
the year (for example, "0131" to 31 ) |
24 |
mmddyy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
25 |
mmddyyyy |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "01010001" to 366 ) |
26 |
mmm |
Converts a 3-letter month abbreviation to a value between 1-12
(for example, "MAR" to 3 )
[Note this is the same as conversion keyword 8] |
27 |
mmmdd |
Converts a date to the number of days since the beginning of
the year (for example, "JAN31" to 31 ) |
28 |
mmmddyy |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
29 |
mmmddyyyy |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "FEB010001" to 32 ) |
30 |
mmmyy |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (1900 is automatically
added to the year if yy is less than or equal to
20; otherwise 2000 is added) |
31 |
mmmyyyy |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (for example, "FEB0001" to 32 ) |
32 |
mmyy |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (1900 is automatically
added to the year if yy is less than or equal to
20; otherwise 2000 is added) |
33 |
mmyyyy |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (for example, "020001" to 32 ) |
34 |
yymm |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (1900 is automatically
added to the year if yy is less than or equal to
20; otherwise 2000 is added) |
35 |
yymmdd |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
36 |
yymmm |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (1900 is automatically
added to the year if yy is less than or equal to
20; otherwise 2000 is added) |
37 |
yymmmdd |
Converts a date to the number of days elapsed since January
1, 0000 (1900 is automatically added to the year if yy is
less than or equal to 20; otherwise 2000 is added) |
38 |
yyyy |
Converts the year the number of years elapsed since the year
0000 (for example, "1998" to 1998 ) |
39 |
yyyymm |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (for example, "000102" to 32 ) |
40 |
yyyymmdd |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "00010201" to 32 ) |
41 |
yyyymmm |
Converts a date to the number of days elapsed between the first
of the specified month and January 1, 0000 (for example, "000102" to 32 ) |
42 |
yyyymmmdd |
Converts a date to the number of days elapsed since January
1, 0000 (for example, "0001FEB01" to 32 ) |
43 |
<day>* <month> |
Converts any delimited date with day followed by month to the
number of days elapsed since the beginning of the year (for example, "15-JAN" to 15 ) |
44 |
<day>* <month>* <year> |
Converts any delimited date with day appearing before month
followed by year to the number of days elapsed since January 1, 0000
(for example, "1/1/0001" to 366 ) |
45 |
<month>* <day> |
Converts any delimited date with month followed by day to the
number of days since the beginning of the year (for example, "JAN
31" to 31 ) |
46 |
<month>* <day>* <year> |
Converts any delimited date with month followed by day followed
by year to the number of days elapsed since January 1, 0000 (for example, "JAN
1, 0001" to 366 ) |
47 |
<month>* <year> |
Converts any delimited date with month followed by year to the number of days elapsed between the first of the specified month and January 1, 0000 |
48 |
<year>* <month> |
Converts any delimited date with year followed by month to the number of days elapsed between the first of the specified month and January 1, 0000 |
49 |
<year>* <month>* <day> |
Converts any delimited date with month followed by day followed
by year to the number of days elapsed since January 1, 0000 (for example, "0001/01/01" to 366 ) |
50 |
yy |
Converts the year to the number of years elapsed since the
year 0000 (for example, "97" to 97 ) |
51 |
mm |
Converts the month to a value between 1-12 (for example, "SEP" to 9 ) |
52 |
dd |
Converts the day to a value between 1-31 (for example, "28" to 28 ) |
53 |
{January | February | March … } |
Converts a fully spelled-out month name to a value between
1-12 (for example, "March" to 3 )
[Note this is the same as conversion keyword 9] |
54 |
{Sunday | Monday | Tuesday … } |
Converts a fully spelled-out weekday name to a value between
1-7, where Sunday marks the beginning of the week (for example, "Sunday" to 1 ) |
55 |
{Sun | Mon | Tue … } |
Converts a 3-day weekday abbreviation to a value between 1-7,
where Sunday marks the beginning of the week (for example, "Sun" to 1 ) |
Description
NUMBER
converts
text values in the specified data range into numerical values using
the specified format for converting dates and times. If a text string
cannot be parsed using the specified conversion_keyword
, NUMBER
will
generate an error. Format 0 converts the first five characters of
each text string into different number for each unique text string.
This is an easy way to change a column of text into unique classes
for outputs to a classifier.
The delimited formats (conversion keywords 43-49) support any of the following as delimiters:
- / (slash)
- - (dash)
- , (comma)
- " " (space)
- : (colon)
Months can be represented as mm
or mmm
;
days can be represented as d
or dd
;
years can be represented as yy
or yyyy
.
yyyy
instead of yy
.
For backwards compatibility, conversion keywords 1-16, yy
(2-digit
years) automatically have 1900 added. For conversion keywords 17-55, yy
< threshold
automatically
have 2000 added; yy ≥ threshold
automatically have
1900 added.threshold
value
is set in the Data Cleaning tab of the Advanced
Settings window (invoke using Options >
Settings > Advanced Settings).NUMBER
macro function to
manipulate date values with 2-digit years. To force an update of a
macro function, you can make any edit (for example, adding a space
and deleting it) and clicking the check mark icon to accept the change.00E+00
).Examples
TEMP = NUMBER("$1.23") or TEMP
= NUMBER("123%", 2) Creates a new column named |
TEMP = NUMBER(column("Jan", "Mar", "Dec",
8) Creates a new column named |
TEMP = NUMBER("1:52 PM", 14) Creates
a new column named |
TEMP = NUMBER("1/1/95", 5) Creates
a new column named |
TEMP = NUMBER(V1) Creates a new column named |
TEMP = NUMBER(V1:V3, 4) Creates three new
columns named |
TEMP = NUMBER(V1[10:20]:V2, 10) Creates
two new columns named |
TEMP = NUMBER(V1, 0) Assuming that column |
Related functions
Function | Description |
---|---|
WEEKDAY |
Converts ASCII text date strings to the day of the week |