Formula Operations

isBusinessTime(dateTime)

inRange(value, startLimit, endLimit) //start and end limit inclusive

Find the index of a value in (sorted) array if it were to replace a value; preference can be BS_LOWEST, BS_HIGHEST, or BS_ANY:

binSearchIndex(array, value, equalsPreference)

column.subArray(startIndex, endIndex) //start-inclusive, end-exclusive

nullToValue(column, value) //sets null values in column to specified value

r = new Random() //generates random values for any numeric primitive type

Note: use with.update method only to avoid undefined results

r.nextInt()

r.nextLong()

r.nextFloat()

r.nextDouble()

Extract substring starting with the first instance of specified character:

str.substring(str.indexOf(character))

Compute average distance:

Example data

t = emptyTable(100).update("Id=(int)(i/4)","X=Math.random()","Y=Math.random()")

//Compute the average distance between x and y values within a group
aveDistance = {x,y ->
   sum = 0.0

    for(i=0; i<x.size(); i++){
   sum += Math.abs(x.get(i)-y.get(i))
    }

   return sum/x.size()
}

//Group the data by Id and then compute the average distance
summary = t.by("Id").update("AveDistance = aveDistance.call(X,Y)").dropColumns("X","Y")

Applying Row Index

Note: ii represents positional row index and should not be used with ticking data.

t.update("Index=ii")

Placing an underscore after a column will allow array access for its values:

t.update("Difference=Column - Column_[ii - 1]")

Create percentile bins based on row index:

upperBin(ii / Column_.size() * 100, 1)

Math

sqrt(input)

abs(input)

Calculate exponential moving average without grouping/ungrouping behaviors:

ByEma.BadDataBehavior.BD_RESET or BD_SKIP or BD_PROCESS modes: AbstractMa.Mode.TICK or TIME:

ema = new ByEmaSimple(nullBehavior, nanBehavior, mode, timescale, timeUnit)

ema.update(Timestamp, Value)

Checks that value isn't null, NaN, or infinite:

isNormal(value)

Working with Time

Note: Single quotes (') rather than backticks (`) are used for times within strings.

String Formats

yyyy-mm-ddThh:mm:ss.nanos TZ //date-time

#y#m#w#dT#h#m#s //period

hh:mm:ss.nanos //duration

Constants representing nanoseconds:

threeSeconds = 3 * SECOND

fiveMinutes = 5 * MINUTE

twoHours = 2 * HOUR

sevenDays = 7 * DAY

Arithmetic

t.updateView("TimeSum = Timestamp + '3y7mT12h'") //period

t.updateView("TimeSum = Timestamp + '01:30:00'") //duration

t.updateView("TimeSum = Timestamp + 6 * HOUR") //nanosecond constants

Downsampling

Round timebin up and get last:

t.updateView("TimeBin=upperBin(Timestamp, TimeLength)")
.lastBy("Sym", "TimeBin").dropColumns("TimeBin")

Round timebin down and get first:

t.updateView("TimeBin=lowerBin(Timestamp, TimeLength)")
.firstBy("Sym", "TimeBin").dropColumns("TimeBin")

Note: TimeLength can use duration or nanosecond constants.

Useful Methods

currentDateNy()

lastBusinessDateNy()

formatDateNy(DateTime) //converts to a date string

currentTime()

Return string representation of date a specified number of days previous:

previousBusinessDay("Date", numberOfDays)

previousBusinessDay(DateTime, numberOfDays)

Last Updated: 23 September 2019 12:17 -04:00 UTC Deephaven v.1.20181212 (See other versions)