All 1010data Functions

1010data provides a whole range of functions that can provide information and act on your data.

Time/Date functions

date(X): Returns the date portion of a date+time value.
datetime(X;Y): Returns the date+time value corresponding to a given date and time.
day(X): Returns the day portion of a date-related value.
dayofwk(X): Returns the integer value corresponding to the day of the week of a date-related value.
dayofyr(P): Returns the integer value corresponding to the day of the year of a date-related value.
days(X;Y): Returns the number of days between two date-related values.
dtunix(X;Y): Return a 1010 timestamp (date+time) value given an integer, bigint, or floating-point value representing Unix-epoch time.
firstdate(X): Returns the date of the first day of the period specified by a date-related value.
fromdate(X;Y): Formats a date in the form YYYYMMDD as a string according to a template.
fromdatetime(X;Y): Formats a date+time in the form XXXX.YYYYYYYYYYY as a string according to a template.
fromtime(X;Y): Formats a time value in the form HHMMSS as a string according to a template.
gregor(X): Returns the date corresponding to the given number of days before the epoch (1/1/2035).
hour(X): Returns the hour portion of a time-related value.
isodayofwk(X): Returns the ISO-8601 day of the week for a given date or timestamp.
isoweek(X): Returns the ISO-8601 week-numbering week for a given date or timestamp.
isoyear(X): Returns the ISO-8601 week-numbering year for a given date or timestamp.
julian(X): Returns the number of days remaining until the epoch for a date-related value (using a similar convention to the Julian date).
lastdate(X): Returns the date of the last day of the period specified by a date-related value.
logindate(X): Returns the date of the current session's login within a given time zone.
logindatetime(X): Returns the date and time of the current session's login within a given time zone.
minute(X): Returns the minutes portion of a time-related value.
mkdate(Y;M;D): Make a date value from a year, month, and day.
mkdatetime(Y;M;D;HH;MM;SS): Make a date+time (timestamp) value from year, month, day, hour, minute, and second numbers.
mkdays(W;D;HH;MM;SS): Make a days (interval) value from weeks, days, hours, minutes, and seconds.
mkmonths(Y;M): Make a months (interval) value from years and months.
mktime(HH;MM;SS): Make a time value from hour, minute, and second numbers.
mkyrmo(Y;M): Make a year+month value from a year and a month.
mkyrqtr(Y;Q): Make a year+quarter value from a year and a quarter.
month(X): Returns the month portion of a date-related value.
months(X;Y): Returns the number of months between two date-related values.
nextwkday(X): Returns the date of the first weekday on or after a date-related value.
periods(X;Y): Returns the number of days, months, quarters, or years between two date-related values, depending on the types of those values.
prevwkday(X): Returns the date of the last weekday on or before a date-related value.
quarter(X): Returns the quarter corresponding to a date-related value.
quarters(X;Y): Returns the number of quarters between two date-related values.
sdayofwk(X): Returns the text value corresponding to the day of the week of a date-related value.
second(X): Returns the seconds portion of a time-related value.
secs(X): Returns the number of seconds from midnight to a time-related value.
shift(X;Y): Returns the date-related value shifted by the amount specified.
shiftmonths(X;Y;E): Returns the date-related value shifted by the number of months specified.
time(X): Returns the time portion of a date+time value.
todate(X;Y): Convert a string, date+time, or date to a date value in the form YYYYMMDD.
todatetime(X;Y): Convert a string, date+time, or date to a date+time value in the form XXXX.YYYYYYYYYYY.
today(X;Y): Convert a string, date+time, or date to a date value in the form DD.
todayname(X;Y): Convert a string, date+time, or date to the conventional name for the day of the week.
todayofwk(X;Y): Convert a string, date+time, or date to a value corresponding to the day of the week.
todayofyr(X;Y): Convert a string, date+time, or date to a value corresponding to the day of the year.
todays(X;Y): Convert a string, months, or days value to a floating-point days interval.
tohour(X;Y): Convert a string, date+time, or time to a time value in the form HH.
tohrmin(X;Y): Convert a string, date+time, or time to a time value in the form HHMM.
tohrmin00(X;Y): Convert a string, date+time, or time to a time value in the form HHMM*100.
tominute(X;Y): Convert a string, date+time, or time to a time value in the form MM.
tomonth(X;Y): Convert a string, date+time, or date to a date value in the form MM.
tomonthname(X;Y): Convert a string, date+time, or date to the conventional name for the month.
tomonths(X;Y): Convert a string, days, or months value to an integer value representing the number of months.
toquarter(X;Y): Convert a string, date+time, or date to an integer value in the form Q.
tosecond(X;Y): Convert a string, date+time, or time to a time value in the form SS.
totime(X;Y): Convert a string, date+time, or time to a time value in the form HHMMSS.
toyear(X;Y): Convert a string, date+time, or date to an integer value in the form YYYY.
toyrmo(X;Y): Convert a string, date+time, or date to a value in the form YYYYMM.
toyrqtr(X;Y): Convert a string, date+time, or date to a value in the form YYYYQ.
unixdt(X;Y): Return a Unix-epoch time value given a string, date, or date+time value.
year(X): Returns the year portion of a date-related value.
years(X;Y): Returns the number of years between two date-related values.
yrmo(X): Returns the month form of a date-related value.
yrqtr(X): Returns the quarter form of a date-related value.

Group functions (g_functions)

aucnt_estimate(M): Returns the approximate number of unique values from an aucnt object. (Available as of version 10.30)
classify(XX;M;Z): Returns the result of classifying data points according to a discrete model.
cparam(M;P;I): Returns the text value of a particular model parameter extracted from a discrete or continuous model.
g_and(G;S;X): Returns a boolean value indicating whether all values within a given group are true.
g_arima(G;S;O;X;P;D;Q;OPTS): Fits an ARIMA(P,D,Q) model to the time series data in column X.
g_arimax(G;S;O;X;VV;P;D;Q;OPTS): Fits an ARIMA(P,D,Q) model to the time series data in column X, with eXogenous variables.
g_asof(G;S;O;X;Y;TX;TY;BA;L): Returns the position of the row within a given group where all the values in one or more columns most closely match the values in another specified set of columns.
g_aucnt(G;S;X;Z): Returns an aucnt object that can be used to compute the approximate unique count of values in a given column. (Available as of version 10.30)
g_aucnt_merge(G;S;M;N): Returns an aucnt object that contains a signature of two or more merged aucnt objects. (Available as of version 10.30)
g_auroc(G;S;X;Y): Returns a model object with four different representations of the Area Under the Receiver Operator Characteristic (AUROC) as well as the perfect model value. (Available as of version 10.42)
g_avg(G;S;X): Returns the average of values within a given group.
g_cdf(G;S;O;X): Returns the cumulative frequency distribution of values within a given group.
g_chase(G;S;X;Y;K;D): Returns a value indicating whether, in a hierarchy of linked nodes, a particular node is reachable from an initial selection of nodes; or, optionally, the smallest number of hops it would take to reach that node from the initial selection.
g_cluster(G;S;XX;A;N;Z): Returns a model corresponding to the clustering of points in data.
g_cnt(G;S): Returns the number of rows within a given group.
g_cor(G;S;X;Y): Returns the correlation coefficient of two sets of values within a given group.
g_cov(G;S;X;Y): Returns the covariance of two sets of values within a given group.
g_cumand(G;S;O;X): Returns a boolean value indicating whether all values up to and including a particular row within a given group are true.
g_cumcnt(G;S;O): Returns the cumulative count within a given group.
g_cumfreq(G;S;O;X): Returns the cumulative frequency of values within a given group.
g_cumhi(G;S;O;X): Returns the highest value for a given group at the point of the current row being evaluated.
g_cumlo(G;S;O;X): Returns the cumulative minimum within a given group.
g_cumor(G;S;O;X): Returns a boolean value indicating whether any value up to and including a particular row within a given group is true.
g_cumprod(G;S;O;X): Returns the cumulative product within a given group.
g_cumrunlen(G;S;O;X): Returns the cumulative run length within a given group.
g_cumsum(G;S;O;X): Returns the cumulative sum within a given group.
g_cumsum_clamp(G;S;O;X;F;C): Returns the cumulative sum within a given group, "clamping" it so that it is never less than or greater than a given value.
g_dot(G;S;X;Y): Returns the dot product of two sets of values within a given group.
g_enum(G;S;O;X): Returns the enumeration of unique values across one or more columns within a given group.
g_fft_im(G;S;O;XR;XI): Returns the imaginary part of fast Fourier transform (FFT) on complex data.
g_fft_pow(G;S;O;X): Returns the fast Fourier transform (FFT) power spectrum for each group
g_fft_re(G;S;O;XR;XI): Returns the real part of the fast Fourier transform (FFT) on complex data.
g_ffti_im(G;S;O;XR;XI): Returns the imaginary part of inverse FFT on complex data.
g_ffti_re(G;S;O;XR;XI): Returns the real part of inverse FFT on complex data.
g_fill(G;S;O;X;D): Returns the value of the nearest non-N/A value before or after the current row within a given group.
g_first(G;S;O;X): Returns the first non-N/A value within a given group.
g_first1(G;S;O): Returns a boolean value indicating whether this is the first row in a given group.
g_forecast(G;S;O;X;VV;M;OPTS;R): Shows fitted values and forecasts future values of a time series X using the model object in the M argument.
g_glm(G;S;Y;XX;Z): Returns a generalized linear model corresponding to the regression of a dependent variable with one or more independent variables. (Available as of prod-9)
g_hi(G;S;X): Returns the highest value within a given group.
g_info_iv(G;S;X;Y): Returns the information value (IV) of X provided Y. (Available as of version 10.42)
g_info_woe(G;S;X;Y): Returns the information theoretic value weight of evidence (WoE) of X provided Y. (Available as of version 10.42)
g_interp(G;S;O;X;N): Returns interpolations for N/A values in a given group.
g_last(G;S;O;X): Returns the last non-N/A value within a given group.
g_last1(G;S;O): Returns a boolean value indicating whether this is the last row in a given group.
g_liblinear(G;S;Y;XX;Z): Returns a model of a given data set using one of the 10 supported underlying algorithms, which include logistic regression as well as both support vector classification and regression. (Available as of prod-9)
g_linrecur(G;S;O;T;N;X;M;K): Multivariate linear recurrence relations of arbitrary order
g_lo(G;S;X): Returns the lowest value within a given group.
g_logreg(G;S;Y;XX;Z): Returns a model corresponding to the logistic regression of one or more independent variables against a given dependent variable.
g_lsq(G;S;Y;XX): Returns a model corresponding to the multiple least squares regression of one or more independent variables against a given dependent variable.
g_lst(G;S;O;X): Returns a list value for each group, in the specified order.
g_lstpkg(G;S;O;X): Returns a list value for each group, in the specified order.
g_markov(G;S;O;T;H;D;M): Returns the results of running a Monte Carlo Markov-chain simulation within a given group.
g_matrix(G;S;O;X;T): Returns a matrix model consisting of the data from the specified columns within a given group. (Available as of prod-9)
g_md5sum(G;S;O;X): Returns the MD5 hash of data for each group within a given column.
g_median(G;S;X): Returns the median of values within a given group.
g_mode(G;S;X): Returns the mode of values within a given group.
g_movavg(G;S;O;X;W;P): Returns the average of valid (non-N/A) values in a moving window within a given group.
g_movcnt(G;S;O;X;W;P): Returns the number of valid (non-N/A) values in a moving window within a given group.
g_movhi(G;S;O;X;W;P): Returns the maximum of valid (non-N/A) values in a moving window within a given group.
g_movlo(G;S;O;X;W;P): Returns the minimum of valid (non-N/A) values in a moving window within a given group.
g_movprod(G;S;O;X;W;P): Returns the product of valid (non-N/A) values in a moving window within a given group.
g_movsum(G;S;O;X;W;P): Returns the sum of valid (non-N/A) values in a moving window within a given group.
g_movvar(G;S;O;X;W;P): Returns the variance of valid (non-N/A) values in a moving window within a given group.
g_nacnt(G;S;X): Returns the number of N/A values within a given group.
g_ntile(G;S;O;X;N): Returns an integer representing the quantile interval (or "bucket") for each row that is a member of a given group based on the values in a particular column. Resultant buckets are of roughly equal size.
g_or(G;S;X): Returns a boolean value indicating whether any value within a given group is true.
g_outlier(G;S;X;Z): Returns a numeric value indicating outliers or extreme values. (Available as of version 10.44)
g_pack(G;S;O;X;N;A): Returns the bin assignment resulting from applying the specified bin packing algorithm for each value in the given column.
g_pca(G;S;XX;Z): Returns a model corresponding to the principal component analysis of one or more variables.
g_pick(G;S;O;X;Y): Returns the value corresponding to a specific position in a particular column within a given group.
g_pkg(G;S;O;K;X): Returns a package value for each group, in the specified order.
g_position(G;S;O;X;Y): Returns the position of the first row within a given group where all the values in one or more columns match the values in another specified set of columns.
g_prod(G;S;X): Returns the product of values within a given group.
g_prodm(G;S;X): Returns the element-wise product (Hadamard product) of matrix values within a given group. (Available as of prod-9)
g_quantile(G;S;X;P): Returns the quantile for values within a given group.
g_rank(G;S;O;X): Returns the rank of every value within a given group.
g_rankavg(G;S;X): Returns the rank of unique values within a given group, averaging the rank for repeated values.
g_rankskip(G;S;X): Returns the rank of unique values within a given group, skipping the rank after repeated values.
g_rankuniq(G;S;X): Returns the dense rank of unique values within a given group, giving repeated values the same rank.
g_rshift(G;S;O;X;N): Returns the value of the row that is a specified number of rows before (or after) the current row within a given group.
g_rshifti(G;S;O;N): Returns a zero-indexed row number that is a specified number of rows before (or after) the current row within a given group (rather than the column value).
g_runs(G;S;O;X): Returns the number of times that a value has changed from row to row within a given group.
g_sarima(G;S;O;X;SP;P;D;Q;PP;DD;QQ;OPTS): Perform Seasonal ARIMA.
g_sarimax(G;S;O;X;VV;SP;P;D;Q;PP;DD;QQ;OPTS): Perform Seasonal ARIMA with eXogenous variables. Since ARIMA is a special case of SARIMAX, g_sarimax can fit the entire family of models.
g_splice(G;S;O;X;D;N): Returns a concatenated list of values ordered in a specified manner within a given group.
g_std(G;S;X): Returns the population standard deviation of values within a given group.
g_std_s(G;S;X): Returns the sample standard deviation of values within a given group.
g_stl(G;S;O;X;NP;OPTS;R): Performs a time-series decomposition on the column X using the STL (Seasonal-Trend decomposition using LOESS).
g_sum(G;S;X): Returns the sum of values within a given group.
g_summ(G;S;X): Returns the element-wise sum of matrix values within a given group. (Available as of prod-9)
g_sumsqr(G;S;X): Returns the sum of squares of the values within a given group.
g_tshift(G;S;T;TS;X;N): Returns the value of the first row that is a specified time period before (or after) the current row within a given group.
g_tshift_nearest(G;S;T;TS;X;N): Returns the value of the nearest row that is at least a specified time period before (or after) the current row within a given group.
g_twindow(G;S;T;TS;N): Returns the number of rows to the row that is exactly a certain time period before (or after) the current row within a given group.
g_twindow_nearest(G;S;T;TS;N): Returns the number of rows to the nearest row that is at least a specified time period before (or after) the current row within a given group.
g_ucnt(G;S;X): Returns the number of distinct values within a given group.
g_uval(G;S;X;D;N): Returns a concatenated list of distinct values within a given group.
g_valcnt(G;S;X): Returns the number of valid (non-N/A) values within a given group.
g_var(G;S;X): Returns the population variance of values within a given group.
g_var_s(G;S;X): Returns the sample variance of values within a given group.
g_wavg(G;S;X;Y): Returns the weighted average of values within a given group.
g_wlsq(G;S;Y;W;XX): Returns a model corresponding to the weighted multiple least squares regression of one or more independent variables against a given dependent variable.
g_wstd(G;S;X;Y): Returns the weighted standard deviation of values within a given group.
g_wvar(G;S;X;Y): Returns the weighted variance of values within a given group.
param(M;P;I): Returns the numerical value of a particular model parameter extracted from a discrete or continuous model.
score(XX;M;Z): Returns the result of scoring data points according to a continuous model.
select(V;I): Returns an element extracted from a vector value

Math functions

abs(X): Returns the absolute value of a given value.
acos(X): Returns the arccosine (in radians) of the given value.
asin(X): Returns the arcsine (in radians) of the given value.
atan(X): Returns the arctangent (in radians) of the given value.
atan2(Y;X): Returns the two-argument arctangent function (equal to atan(y/x) when x is positive) as an angle expressed in radians.
beta_cdf(A;B;X): Returns the beta cumulative distribution function with shape parameter A, rate parameter B, and value X.
betainc(A;B;X): Returns the regularized incomplete beta function of the given values.
between(X;Y;Z): Returns a boolean value indicating whether a given value is between two other values.
bigint(X): Returns the given value cast to a big integer data type. (Available as of version 11.11)
bincoeff(N;K): Returns the binomial coefficient of two given values.
binomial_cdf(N;K;P): Returns the binomial cumulative distribution function value with N-trials, K-successes, and success probability P.
bitand(X;Y): Returns the bitwise AND of two 32-bit integers.
bitlsl(X;Y): Returns the logical shift left of a given 32-bit integer by a specified number of bits.
bitlsr(X;Y): Returns the logical shift right of a given 32-bit integer by a specified number of bits.
bitnot(X;Y): Returns the bitwise NOT operation on the specified least significant bits of a given 32-bit integer.
bitor(X;Y): Returns the bitwise OR of two 32-bit integers.
bitrol(X;Y): Returns the given 32-bit integer rotated left by a specified number of bits.
bitror(X;Y): Returns the given 32-bit integer rotated right by a specified number of bits.
bitset(X;Y): Returns a boolean value indicating whether a particular bit of a given 32-bit integer is set.
bitxor(X;Y): Returns the bitwise XOR (exclusive-OR) of two 32-bit integers.
ceil(X): Returns the smallest integer greater than or equal to the given value. The result is a decimal data type.
chisqr_cdf(DF;X): Returns the chi squared cumulative distribution function with degrees of freedom DF and value X.
clamp(X;Y;Z): Returns Y if X<Y and Z if X>Z. Otherwise, if the "clamped" value X is between Y and Z, clamp(X;Y;Z) returns X.
clip(X;Y;Z): Returns NA if X is "out of bounds", that is, X<Y or X>Z. Otherwise, clip(X;Y;Z) returns X.
clipout(X;Y;Z): Returns NA if X is "in bounds", that is, X>=Y or X<=Z. Otherwise, clipout(X;Y;Z) returns X.
clrbit(X;Y): Returns the value of the given 32-bit integer with a particular bit cleared.
combs(N;K): Returns the number of combinations of two given values.
cos(X): Returns the cosine of the given value.
cosh(X): Returns the hyperbolic cosine of the given value.
draw(X;Y): Returns a random number using a given seed.
draw_(X;Y): Returns a random number using a given seed that remains persistent (sticky) even after selections or links with expansion are used.
draw_int(X;Y): Returns a random integer using a given seed that remains persistent (sticky) even after selections or links with expansion are used.
draw_rand(X): Returns a random number using a given seed that remains persistent (sticky) even after selections or links with expansion are used.
draw_unif(X;A;B): Returns a random float between A and B, using a given seed that remains persistent (sticky) even after selections or links with expansion are used.
drawint(X;Y): Returns a random integer between 0 and Y-1, seeded with X.
drawrand(X): Returns a random float between 0 and 1, seeded with X.
drawunif(X;A;B): Returns a random float between A and B, using seed X.
erf(X): Returns the Gaussian error function of a given value.
erlang_cdf(K;L;X): Returns the Erlang cumulative distribution function value with rate L and shape K at value X.
exp(X;Y): Returns the first value raised to the power of the second value.
expe(X): Returns the exponential of the given value.
exponential_cdf(L;X): Returns the exponential cumulative distribution function value with mean L at value X.
f_cdf(DF1;DF2;F): Returns the F cumulative distribution function with degrees of freedom DF1 and DF2 at F.
fact(X): Returns the factorial of a given value.
floor(X): Returns the largest integer less than or equal to the given value. The result is a decimal data type.
gamma(X): Returns the gamma function of the given value.
gamma_cdf(A;B;X): Returns the gamma cumulative distribution function with shape parameter A, rate parameter B, and value X.
gammainc(A;X): Returns the incomplete gamma function of the given values.
idiv(X;Y): Returns the quotient after dividing one integer value by another. The result is an integer data type.
imod(X;Y): Returns the remainder after dividing one integer value by another. The result is an integer data type.
int(X): Returns the largest integer less than or equal to the given value. The result is an integer data type.
interp(V;N;X;Y;E): Returns a particular interpolation of a given value based on a discrete set of data points, and if specified, extrapolates out-of-range values.
log(X;Y): Returns the logarithm of the first value to the base of the second value.
loge(X): Returns the natural logarithm of the given value.
logfact(X): Returns the natural log of the factorial of a given value.
loggamma(X): Returns the natural log of the gamma function of the given value.
max(X;Y): Returns the larger of two given values.
min(X;Y): Returns the smaller of two given values.
mod(X;Y): Returns the modulo of two given values.
normal01_rand_devs(X): Returns a random number for each row in the column drawn from a normal distribution with a mean of 0 and a standard deviation of 1.
normal_cdf(X;U;S): Returns the cumulative distribution function value at X for a normal distribution.
normal_cdf_inv(P;U;S): Returns the inverse of the normal_cdf at P.
perms(N;K): Returns the number of permutations of two given values.
poisson_cdf(L;K): Returns the Poisson cumulative distribution function for a given mean.
real(X): Returns the given value cast to decimal data type.
round(X;Y): Returns the result of rounding the first given value to the nearest multiple of the second given value.
setbit(X;Y): Returns the value of the given 32-bit integer with a particular bit set.
signum(X): Returns the sign number (-1, 0, or 1) of a given value.
sin(X): Returns the sine of the given value.
sinh(X): Returns the hyperbolic sine of the given value.
sqr(X): Returns the given value squared.
sqrt(X): Returns the square root of the given value.
studentt_cdf(DF;T): Returns the Student's T cumulative distribution function with degrees of freedomDF and value T.
svvs(X;Y;Z;I;B): Decode and then encode a value based on an index.
tan(X): Returns the tangent of the given value.
tanh(X): Returns the hyperbolic tangent of the given value.
trunc(X;Y): Returns the result of rounding the first given value to the multiple of the second given value, always rounding in the direction of 0.

Matrix functions

g_matrix(G;S;O;X;T): Returns a matrix model consisting of the data from the specified columns within a given group. (Available as of prod-9)
g_prodm(G;S;X): Returns the element-wise product (Hadamard product) of matrix values within a given group. (Available as of prod-9)
g_summ(G;S;X): Returns the element-wise sum of matrix values within a given group. (Available as of prod-9)
matacos(X): Returns the arccosine (in radians) of matrix X.
matasin(X): Returns the arcsine (in radians) of matrix X.
matatan(X): Returns the arctangent (in radians) of matrix X.
matcos(X): Returns the cosine of matrix X.
matcosh(X): Returns the hyperbolic cosine of matrix X.
matdivide(X;Y): Returns the element-wise division of the two given matrices. (Available as of prod-9)
mateq(X;Y): Returns a boolean value indicating the equivalence of the two given matrices. (Available as of prod-9)
matexp(X): Returns the matrix exponential of matrix X.
matgamma(X): Returns the gamma function of matrix X.
matinv(X): Returns the inverse of the given matrix. (Available as of prod-9)
matlgamma(X): Returns the natural log of the gamma function of matrix X.
matlog(X): Returns the logarithm of matrix X.
matminus(X;Y): Returns the element-wise difference of the two given matrices. (Available as of prod-9)
matmul(X;Y): Returns the matrix multiplication of the two given matrices. (Available as of prod-9)
matplus(X;Y): Returns the element-wise sum of the two given matrices. (Available as of prod-9)
matrint(X): Rounds values in a matrix to integers.
matsin(X): Returns the sine of matrix X.
matsinh(X): Returns the hyperbolic sine of matrix X.
matt(X): Returns the transpose of the given matrix. (Available as of prod-9)
mattan(X): Returns the tangent of matrix X.
mattanh(X): Returns the hyperbolic tangent of matrix X.
mattimes(X;Y): Returns the element-wise product (Hadamard product) of the two given matrices. (Available as of prod-9)
r_prodm(C;S): Returns the element-wise product (Hadamard product) of matrix values across a given set of columns. (Available as of prod-9)
r_summ(C;S): Returns the element-wise sum of matrix values across a given set of columns. (Available as of prod-9)

Categorization functions

case(X;V1;R1;V2;R2;...;D): Returns the value corresponding to the case that a given value matches; if no cases match, returns the specified default value.
decode(X;V1 V2 ...;R1 R2 ... RD): Returns the value from the second list that corresponds to the particular element a given value matches in the first list; if there are no matches, returns the specified default value.
g_and(G;S;X): Returns a boolean value indicating whether all values within a given group are true.
g_cumand(G;S;O;X): Returns a boolean value indicating whether all values up to and including a particular row within a given group are true.
g_cumor(G;S;O;X): Returns a boolean value indicating whether any value up to and including a particular row within a given group is true.
g_or(G;S;X): Returns a boolean value indicating whether any value within a given group is true.
g_pack(G;S;O;X;N;A): Returns the bin assignment resulting from applying the specified bin packing algorithm for each value in the given column.
if(C1;R1;C2;R2;...;D): Returns the value corresponding to the first condition that evaluates to true; if no conditions are true, it returns the specified default value.
iff(C1;R1;C2;R2;...;D): Returns the value corresponding to the first condition that evaluates to true; if no conditions are true, it returns the specified default value.
ifinf(X;Y): Returns a specified value when the value in a given column is an infinite value.
ifnull(X;Y): Returns a specified value when the value in a given column is N/A.
nullif(X;Y): Returns N/A if the two given values match.
nullinf(X): Returns N/A if the given value is an infinite value.
range1(X;S;E;I): Returns the upper bound of the particular interval in which a given value falls within a specified range of values.
range1f(X;S;E;I;D): Returns the textual representation of the interval in which a given value falls within a specified range of values.
range2(X;L): Returns the smallest number in a list of numbers that is greater than or equal to a given value.
range2f(X;L;D): Returns the textual representation of which of the intervals, defined by a specified list of numbers, that a given value falls.
range2i(X;L): Returns the number corresponding to which of the intervals, defined by a specified list of numbers, a given value falls.
range2list(X;L;R): Returns a value from a list of numeric or text values that indicates which of the intervals, defined by a specified list of numbers, that a given value falls.
rep(X;L): Returns the value paired up with the first item in a specified list that matches the given value.

String functions

beginswith(X;L): Returns a boolean value indicating whether a given string begins with any of the substrings in a given list.
beginswith_ci(X;L): Returns a boolean value indicating whether a given string begins with any of the substrings in a given list and is case insensitive. (Available as of version 12.33)
between(X;Y;Z): Returns a boolean value indicating whether a given value is between two other values.
between_ci(X;Y;Z): Returns a boolean value indicating whether a given value is between two other values and is case insensitive.
bstrclean(X): Returns a boolean value indicating whether the given string is plain (7-bit) ASCII (i.e., "8-bit clean").
colunion(X;Y): Returns a string consisting of the union of values from a given set of columns concatenated together using the specified separator, sorted in ascending order.
contains(X;L): Returns a boolean value indicating whether a given string contains any of the substrings in a given list.
contains_ci(X;L): Returns a boolean value indicating whether a given string contains any of the substrings in a given list and is case insensitive. (Available as of version 12.33)
dexml(S;O): Converts an XML string into a 1010data XML value or list of values (str → xml). (Available as of version 11.07)
dropto(X;Y): Returns the remaining part of a given string after the characters up to and including the first occurrence of a particular substring are dropped.
edistnorm(X;Y): Returns the normalized Levenshtein edit distance between two given strings.
editdist(X;Y): Returns the Levenshtein edit distance between two given strings.
endswith(X;L): Returns a boolean value indicating whether a given string ends with any of the substrings in a given list.
endswith_ci(X;L): Returns a boolean value indicating whether a given string ends with any of the substrings in a given list and is case insensitive. (Available as of version 12.33)
enhtml(X;Z): Returns an HTML-encoded version of the given string. (Available as of version 11.25)
enxml(V;O): Converts a 1010data XML value, or a list of values, into an XML string (xml → str). (Available as of version 11.07)
first(X;Y): Returns a string consisting of the first number of characters of a given string.
g_splice(G;S;O;X;D;N): Returns a concatenated list of values ordered in a specified manner within a given group.
g_uval(G;S;X;D;N): Returns a concatenated list of distinct values within a given group.
last(X;Y): Returns a string consisting of the last number of characters of a given string.
leftstr(X;Y): Returns a string consisting of the first number of characters of a given string, or all but the last number of characters of a given string.
lowercase(X): Returns a given string converted to all lowercase.
lst_to_str(L;D): Returns a delimiter-separated string comprised of the elements in a list-value.
midstr(X;Y;Z): Extracts up to Z characters starting at the Yth character from the end of the string.
padleft(X;Y): Returns the given string with a certain number of characters, adding blanks at the beginning if necessary.
padright(X;Y): Returns the given string with a certain number of characters, adding blanks at the end if necessary.
prettyxml(X;): Converts an XML special value X into a fully formatted (indented, etc.) text representation. (Available as of version 16.13)
propercase(X): Returns the given string with the first letter, as well as any other letter that follows any character other than a letter, capitalized, and converts all other letters to lower case.
qv(X): Returns a properly quoted and escaped string for insertion in Macro Language code.
r_splice(C;S;D): Returns a string consisting of a list of elements across a given set of columns, concatenated together using the specified delimiter.
regex_beg(X;L;Y;I): Returns the beginning position with respect to a particular match of a specified regular expression within a given string.
regex_count(X;L;Y): Returns the number of matches of a specified regular expression within a given string.
regex_end(X;L;Y;I): Returns the position following a particular match of a specified regular expression within a given string.
regex_match(X;L;Y;I): Returns the substring corresponding to a particular match of a specified regular expression within a given string.
regex_subst(X;L;Y;I;Z): Returns the given string with a particular match (or all matches) of a specified regular expression replaced by a given substring.
repstr(X;L): Returns the given string with substrings replaced based on a given list of string pairs.
rightstr(X;Y): Returns a string consisting of the last number of characters of a given string, or all but the first number of characters of a given string.
rm(X;Y): Returns a boolean value indicating whether a given string matches a standard regular expression.
rm_ci(X;Y): Returns a boolean value indicating whether a given string matches a standard regular expression and is case insensitive.
sm(X;Y): Returns a boolean value indicating whether a given string matches a particular template, and is case sensitive.
sm_ci(X;Y): Returns a boolean value indicating whether a given string matches a particular template and is case insensitive.
splice(X;Y): Returns a string consisting of the list of given values concatenated together using the specified separator.
str_to_lst(S;D): Returns a list-value from a delimiter-separated string.
strclean(X): Returns a boolean value indicating whether the given string is a legal UTF-8 (Unicode) string.
strcmp(X;Y;B): Returns an integer value that indicates the results of comparing two strings.
strcount(X;Y): Returns the number of occurrences of a given substring within a given string.
strdist(X;Y;M;N): Returns the edit distance between two given strings using a specified method.
strdowncase(X): Returns the given string with all uppercase characters converted to lowercase.
strdrop(X;Y;N): Returns all but the first number of characters, or the substring consisting of all but the first number of elements delimited by a particular value, within a given string.
stredit(X;P;N;Y): Returns a given string, with a specified number of characters starting at a certain position replaced by a given substring.
strembed(X;N;P;Y;D): Returns a given string embedded within a template that consists of a string value repeated a particular number of times.
strextract(X;P;N): Returns the specified number of characters, starting at a certain position, extracted from a given string.
strfind(X;Y;I): Returns the position within a given string of a particular occurrence of a given substring.
strfollow(X;Y;B): Returns the first position where two strings differ from one another, or 0 if they are equal.
string(X): Returns the input value expressed as a text string.
strlcs(X;Y): Returns the longest contiguous substring that appears in two given strings.
strlcslen(X;Y): Returns the length of the longest contiguous substring that appears in two given strings.
strlcspos(X;Y): Returns the position in the second string of the longest contiguous substring that appears in both given strings.
strlen(X): Returns the number of characters in a given string.
strmap(X;Y;Z;C): Returns the given string with certain characters replaced or deleted according to a particular mapping.
strpick(X;Y;I): Returns the character at a particular position, or the element delimited by a particular value, within a given string.
strrange(X): Returns the unique characters of a given string sorted in ascending order.
strrepeat(X;N): Return the given string repeated a certain number of times.
strreverse(X;Y): Returns the characters, or the elements delimited by a particular value, of a given string in reverse.
strsort(X;Y;U;D): Returns the characters, or the elements delimited by a particular value, of a given string sorted in ascending or descending order.
strspan(X;Y;C;D): Returns the position of the first (or last) character in a given string that is found (or not found) in another string.
strsplit(X;Y;I): Returns the character at a particular position, or the substring delimited by a particular value, within a given string.
strsplitpos(X;Y;I): Returns the position of a certain element delimited by a particular value within a given string.
strsubst(X;Y;I;Z): Returns the given string with a particular occurrence (or all occurrences) of a given substring replaced with a different substring.
strtake(X;Y;N): Returns the first number of characters, or the substring consisting of the first number of elements delimited by a particular value, within a given string.
strtrim(X;Y;C;D): Returns the given string with certain characters trimmed from either or both ends.
strupcase(X): Returns the given string with all lowercase characters converted to uppercase.
taketo(X;Y): Returns the beginning part of a given string up to but excluding the first occurrence of a particular substring.
transcode(X;F;T;S;O): Converts a string from one encoding into a different encoding.
uppercase(X): Returns a given string converted to all uppercase.
urldecode(X): Converts raw URI, URL, or HTTP messages with hex-encoded values into human-readable strings.
width(X): Returns the string length of the input value.

List functions

conjsel(L): Returns a selection expression that consists of the conjunction of the pairs of columns and associated values specified in the given list-value. (Available as of version 11.06)
csl_drop(X;Y): Returns all but the first number of elements in a given comma-separated list.
csl_len(X): Returns the number of elements in a given comma-separated list.
csl_pick(X;Y): Returns a particular element in a given comma-separated list.
csl_take(X;Y): Returns the first number of elements in a given comma-separated list.
dejson(S;O): Converts a JSON string into 1010data values. (Available as of version 11.07)
dexml(S;O): Converts an XML string into a 1010data XML value or list of values (str → xml). (Available as of version 11.07)
encsv(X;FD;RD;QC;O): Converts a table or query value X into CSV or other delimiter-separated text. (Available as of version 19.77)
enjson(V;O): Converts values, including packages and lists, into a JSON string. (Available as of version 11.07)
enxml(V;O): Converts a 1010data XML value, or a list of values, into an XML string (xml → str). (Available as of version 11.07)
exlst(L;E): Remove the contents of list-value E from list-value L.
inlst(L1 L2 LN): Find the intersection of two or more list-variables.
jnlst(L1 L2...LN): Returns a single list-value made up of the elements of multiple lists.
lst(X): Returns a single, indexed list-value when given one or more values.
lst_del(L;D): Remove the elements in list-value L at the index positions specified in list-value D.
lst_drop(L;N): Returns a list-value of all elements after a given element.
lst_len(L): Returns the number of elements in a list-value.
lst_pick(L;I): Find the value of an element in a list-value from a specified index position.
lst_position(L;P): Find the index positions of values in list-value L for values that appear in list-value P.
lst_remove(L;R): Remove elements from list-value L that are found in list-value R. This function does not remove duplicates that appear multiple times in list-value L from the returned list-value.
lst_reverse(L): Invert the order of the elements of a list-value.
lst_take(L;N): Returns a list-value containing a given number of elements.
lst_to_pkg(K;V): Returns a new package value by accepting a list of keys and a list of values of equal lengths.
lst_to_str(L;D): Returns a delimiter-separated string comprised of the elements in a list-value.
mklst(N;X): Returns a single, indexed list-value containing multiple instances of the given value.
param(M;P;I): Returns the numerical value of a particular model parameter extracted from a discrete or continuous model.
pick(X;L): Returns the item in a specified list at the position denoted by the given value.
pkg(K;V): Returns a single, indexed list-value containing key-value pairs.
pkg_default(P;K;D): Returns a value from a package at a given key position, or returns a default value if the value is invalid.
pkg_get(P;K): Returns a value from a package at a given key position.
pkg_names(X): Returns a single, indexed list-value containing the key values from a specified package variable.
position(X;L): Returns the position of a given value in a specified list.
qv(X): Returns a properly quoted and escaped string for insertion in Macro Language code.
str_to_lst(S;D): Returns a list-value from a delimiter-separated string.
strdrop(X;Y;N): Returns all but the first number of characters, or the substring consisting of all but the first number of elements delimited by a particular value, within a given string.
strpick(X;Y;I): Returns the character at a particular position, or the element delimited by a particular value, within a given string.
strreverse(X;Y): Returns the characters, or the elements delimited by a particular value, of a given string in reverse.
strsort(X;Y;U;D): Returns the characters, or the elements delimited by a particular value, of a given string sorted in ascending or descending order.
strsplit(X;Y;I): Returns the character at a particular position, or the substring delimited by a particular value, within a given string.
strsplitpos(X;Y;I): Returns the position of a certain element delimited by a particular value within a given string.
strtake(X;Y;N): Returns the first number of characters, or the substring consisting of the first number of elements delimited by a particular value, within a given string.
unlst(L1 L2...LN): Find the union of two or more list-values.

Data-handling functions

dblmetaphone(R;X): Returns the Double Metaphone hash of a given value.
g_md5sum(G;S;O;X): Returns the MD5 hash of data for each group within a given column.
geohash(latitude;longitude;precision): Returns a geohash symbol with a given precision based on the coordinates of a particular location. (Available as of version 10.16)
hash(X;M): Returns a one-way cryptographic hash of a given string using the specified algorithm.
metaphone(X): Returns the Metaphone hash of a given value.
nysiis(X): Returns the NYSIIS hash of a given value.
soundex(X): Returns the Soundex hash of a given value.

Row functions

r_and(C;S): Returns a boolean value indicating whether all the values in a given set of columns are true.
r_avg(C;S): Returns the average of values across a given set of columns.
r_cnt(C;S): Returns the number of columns within a given set of columns.
r_first(C;S): Returns the first valid (non-N/A) value across a given set of columns.
r_hi(C;S): Returns the maximum of values across a given set of columns.
r_last(C;S): Returns the last valid (non-N/A) value across a given set of columns.
r_lo(C;S): Returns the minimum of values across a given set of columns.
r_mapjson(C;S;X;O): Returns a string containing a JSON representation of a package for each row in a table.
r_maplst(C;S;X): Returns a list for each row in a table, in the order the columns appear in the table.
r_mappkg(C;S;X): Returns a package value for each row in a table.
r_mapstring(C;S;X;D): Returns a string consisting of a list of elements, each of which is the result of evaluating an expression on the current column, concatenated together using the specified delimiter.
r_md5sum(C;S): Returns the MD5 hash of data across a given set of columns.
r_mean(C;S): Returns the mean of values across a given set of columns.
r_nacnt(C;S): Returns the number of columns that have N/A values across a given set of columns.
r_or(C;S): Returns a boolean value indicating whether any of the values in a given set of columns are true.
r_over(C;S;A;X): Returns the result of evaluating the expression X for each value in the specified columns.
r_pick(C;S;I): Returns the value of the column at the specified index within a given set of columns.
r_position(C;S;X): Returns the index of the first column in a given set of columns that matches a specified value.
r_prod(C;S): Returns the product of values across a given set of columns.
r_prodm(C;S): Returns the element-wise product (Hadamard product) of matrix values across a given set of columns. (Available as of prod-9)
r_splice(C;S;D): Returns a string consisting of a list of elements across a given set of columns, concatenated together using the specified delimiter.
r_std(C;S): Returns the standard deviation of values across a given set of columns.
r_sum(C;S): Returns the sum of values across a given set of columns.
r_summ(C;S): Returns the element-wise sum of matrix values across a given set of columns. (Available as of prod-9)
r_valcnt(C;S): Returns the number of columns that have valid (non-N/A) values across a given set of columns.
r_var(C;S): Returns the variance of values across a given set of columns.

SQL compatibility functions

sql_and(X;Y): Returns a boolean value indicating whether both X and Y are true. This function has SQL-compatible null handling.
sql_bet(X;Y;Z): Returns a boolean value indicating if X is between Y and Z in ascending order. This function has SQL-compatible null handling.
sql_e(X;Y): Returns a boolean value indicating if X is equal to Y. This function has SQL-compatible null handling.
sql_eqlin(X;Y): Returns a boolean value indicating if X is found in Y. This function has SQL-compatible null handling.
sql_g(X;Y): Returns a boolean value indicating if X is greater than Y. This function has SQL-compatible null handling.
sql_ge(X;Y): Returns a boolean value indicating if X is greater than or equal to Y. This function has SQL-compatible null handling.
sql_l(X;Y): Returns a boolean value indicating if X is less than Y. This function has SQL-compatible null handling.
sql_le(X;Y): Returns a boolean value indicating if X is less than or equal to Y. This function has SQL-compatible null handling.
sql_ne(X;Y): Returns a boolean value indicating if X is not equal to Y. This function has SQL-compatible null handling.
sql_not(X): Returns the logical negation of the argument X. This function has SQL-compatible null handling.
sql_or(X;Y): Returns a boolean value indicating whether X or Y are true. This function has SQL-compatible null handling.

Financial functions

amort(rate;remterm;months): Returns the mortgage amortization.
bondconvexity1(cpn;mat;dcb;setl;price): Returns the bond convexity given the price (regular semiannual payments).
bondconvexity2(cpn;mat;dcb;issu;first;freq;setl;price): Returns the bond convexity given price (general).
bondmoddur1(cpn;mat;dcb;setl;price): Returns the bond modified duration given price (regular semiannual payments).
bondmoddur2(cpn;mat;dcb;issu;first;freq;setl;price): Returns the bond modified duration given price (general).
bondprice1(cpn;mat;dcb;setl;yield): Returns the bond price given yield (regular semiannual payments).
bondprice2(cpn;mat;dcb;issu;first;freq;setl;yld): Returns the Bond price given yield (general).
bondyield1(cpn;mat;dcb;setl;price): Returns the bond yield given price (regular semiannual payments).
bondyield2(cpn;mat;dcb;issu;first;freq;setl;price): Returns the bond yield given price (general).
cpr2smm(X): Returns the SMM (single monthly mortality) given CPR (conditional prepayment rate).
mbscpr(wac;wam;factor1;factor2;months): Returns the conditional prepayment rate (CPR).
mbsmoddur1(type;cpn;wac;wam;setl;pptype;pprate;p): Returns the mortgage-backed security (MBS) modified duration given price (30-year agency pools).
mbsmoddur2(type;cpn;wac;term;wam;wala;dly;setl;pptype;pprate;p): Returns the mortgage-backed security (MBS) modified duration given price (general)
mbsprice1(type;cpn;wac;wam;setl;pptype;pprate;yld): Returns the mortgage-backed security (MBS) price given yield (30-year agency pools).
mbsprice2(type;cpn;wac;term;wam;wala;dly;setl;pptype;pprate;yld): Returns the mortgage-backed security (MBS) price given yield (general)
mbssmm(wac;wam;factor1;factor2;months): Returns the single monthly mortality (SMM). This is the MBS prepayment rate.
mbsyield1(type;cpn;wac;wam;setl;pptype;pprate;p): Returns the mortgage-backed security (MBS) yield given price (30-year agency pools).
mbsyield2(type;cpn;wac;term;wam;wala;dly;setl;pptype;pprate;p): Returns the mortgage-backed security (MBS) yield given price (general).
smm2cpr(X): Returns the CPR (conditional prepayment rate) given SMM (single monthly mortality).

System functions

format(X;Y): Applies a format string to an input column or value.
goodcolname(X): Returns a boolean value indicating whether a given string would be a valid/recommended column name. (Available as of version 11.25)
goodname(X): Returns a boolean value indicating whether a given string would be a valid name for a table, folder, or query. (Available as of version 10.23)
goodpath(X): Returns a boolean value indicating whether a given string would be a valid path. (Available as of version 10.23)
identical(X;Y): Returns a boolean value indicating whether the two arguments are identical. (Available as of version 11.21)
same(X;Y): Returns a boolean value indicating whether the two arguments are equal. (Available as of version 11.21)
tabledata(T;K;I): Returns information about a given table based on a specified key. Certain keys, which return information about particular columns or data segments, require an index.
userdata(U;K): Returns a scalar value of information about a user based on a key. The key represents a piece of data stored in a dictionary that contains information about the UID.
usergroupdata(U): Returns a comma-separated list of the groups in which a user ID is a member. (Available as of version 13.16)

Object functions

obj_children(path): Returns the list of objects contained within the folder at the given path. (Available as of version 10.24)
obj_exists(path): Returns a boolean value that indicates if the object at the given path exists. (Available as of version 10.23)
obj_inherits(path): Returns a boolean value that indicates if user access to the object at the given path is inherited from the parent folder. (Available as of version 10.24)
obj_isfolder(path): Returns a boolean value that indicates if the object at the given path is a folder. (Available as of version 10.23)
obj_isphysical(path): Returns a boolean value that indicates if the object at the given path is a saved physical table. (Available as of version 10.23)
obj_isquery(path): Returns a boolean value that indicates if the object at the given path is a query. (Available as of version 10.23)
obj_istable(path): Returns a boolean value that indicates if the object at the given path is a table. (Available as of version 10.23)
obj_istemp(path): Returns a boolean value that indicates if the object at the given path is a temporary table in the user's session. (Available as of version 10.23)
obj_ldesc(path): Returns the long description of the object at the given path. (Available as of version 10.23)
obj_mode(path): Returns the display mode of the object at the given path. (Available as of version 10.23)
obj_name(path): Returns the name of the object at the given path. (Available as of version 10.24)
obj_own(path): Returns a boolean value that indicates if the object at the given path is owned by the current user. (Available as of version 10.23)
obj_owner(path): Returns the owner of the object at the given path. (Available as of version 10.23)
obj_parent(path): Returns the name of the folder in which the object at the given path is contained. (Available as of version 10.23)
obj_parents(path): Returns the hierarchical list of folders in which the object at the given path is contained. (Available as of version 10.24)
obj_readable(path): Returns a boolean value that indicates if the object at the given path can be accessed by the current user. (Available as of version 10.23)
obj_runnable(path): Returns a boolean value that indicates if the object at the given path can be opened by the current user. (Available as of version 10.23)
obj_sdesc(path): Returns the short description of the object at the given path. (Available as of version 10.23)
obj_title(path): Returns the title of the object at the given path. (Available as of version 10.23)
obj_type(path): Returns the type of the object at the given path. (Available as of version 10.23)
obj_uploaders(path): Returns the list of users that have specifically been given permission to save or upload objects to the folder at the given path. (Available as of version 10.24)
obj_users(path): Returns the list of users that have specifically been given permission to access the object at the given path. (Available as of version 10.24)
obj_writable(path): Returns a boolean value that indicates if the object at the given path can be modified and saved by the current user. (Available as of version 10.23)

Miscellaneous functions

rolledup(X;I): Returns a boolean value indicating whether or not a particular break column is included for the current row within a rollup tabulation.
stemmer(Y;L;P): Returns the stem of a word for a given language.