Overview

Flex.io is a cloud-based framework for rapidly developing and deploying data pipes that automatically process and move data files. The concept of a pipe is simple: select an input, create your data processing steps and then choose an output.

Here is a 3-minute onboarding video to get you started (note: this video will open in YouTube):

Onboarding Video - Youtube Link

Flex.io provides a framework that handles data and piping infrastructure, including:

Within Pipes, you can use various processing tools, including:

Other administrative and support help on Flex.io can be found here:

Documentation for developers can be found here:

Projects

Flex.io Projects are individual repositories that allow you to manage collections of Pipes, Connections and Members. You can create as many Projects as you like.

Project list

The Project List provides an overview of each of your Projects.

Project List

Each Project contains a title, a description and some metadata, such as creation date and number of Pipes and Members.

Click on a Project name to access your Pipes, Connections and Members. You may return to the Project List anytime by clicking the home icon at the top-left of the header.

Adding a new project

To create a new Project, click the “New Project” button at the top-right of the list.

Create New Project

Select a name for the project and, optionally, add a description. Click on the “Create Project” button to add the Project.

Updating a project

To edit Project information, click the menu drop-down to the right of your project name.

Edit Project

Click Edit to update the name or description and click the Save Changes button to confirm.

Deleting a project

To delete the Project, click the menu drop-down to the right of your project name and select the Delete.

Delete Project

A dialog will appear asking you to type in the Project name to confirm your choice. Click the Delete Project button to confirm.

Pipes

Flex.io is a data piping framework. The Pipe List shows all of your Pipes in a given Project and the Pipe Builder enables you to connect Inputs and Outputs as well as add processing steps.

Pipe List

The Pipes area is used to manage all Pipes specific you your Project.

Adding a new pipe

To add a new Pipe to your Project, click on the Pipes tab on the left. Once in the Pipes tab, click on the “New Pipe” button at the top-right of the content area. The new Pipe dialog will appear:

New Pipe

You can edit the following:

Select the “Create Pipe” button to create the pipe. You will then automatically be taken into the pipe builder to add steps to your pipe.

Building and running a pipe

In this section, we’ll walk through how to build a simple pipe, including adding inputs, processing steps and outputs. When you create a new pipe you are taken to the pipe builder.

Pipe Builder

There are two main views to the pipe builder: * Pipe Overview. The overview provides a quick way to set up your input and output. It also displays an overview summary of your pipe steps. * Pipe Builder. The builder provides an interface for creating pipe steps. While you may also create input and output steps in the builder area itself, this section is primary used for adding procesing steps to your pipe and seeing previews as you build.


Add an input and output

When you create a new pipe, you are taken to the Pipe Overview view. This view shows your input and output to the left and a summary of your pipe steps to the right. To create a basic pipe for moving files, you simply need to add an input and output here. Additional pipe steps are optional and can be added in the Pipe Builder view.

Adding an input and output work the same way. Simply click on a connection tile to choose it and follow the prompts to add files, authenticate, etc. Connections that do not require authentication or have previously been authenticated are found under the My Connections header. Additional connections, not yet authenticated can be found under the Available Connections header.

Build Pipe: Input Output

For this example, we’ll choose a web link as the input https://raw.githubusercontent.com/flexiodata/data/master/contact-samples/contacts-ltd1.csv and a Dropbox folder as our output.

After selecting an input and an output, click the Run Pipe button to run the pipe. Running the pipe will take you to the builder view, run the pipe and provide a preview of your data transfer steps.

Build Pipe: Input Output Run

And, that’s it. We’ve built and run our first basic pipe.


Add processing steps

Instead of (or in addition to) running our pipe, we can add additional steps in the Pipe Builder tab. Click the tab to see a step by step view of the pipe.

Build Pipe: Builder View

In this case, we only have a single input and output. In command form, the steps are as follows:

Input:

input file: https://raw.githubusercontent.com/flexiodata/data/master/contact-samples/contacts-ltd1.csv

Output:

output to: gp20wy98fcbz location: /temporary

Now we’ll add a few processing steps to demonstrate the workflow.

First, click the ’+’ tile between step 1 and step 2 and a new command line will appear.

Build Pipe: Add Step

Since the input was a CSV file, we’ll add a Convert Operation that will convert the text to a table. Enter the command below and then click Save Changes to add the step.

convert from: delimited to: table delimiter: comma header: true

Build Pipe: Convert

Saving a step will not automatically re-run your pipe, so may continue on with additional steps. However, to see a preview for each of your pipe steps, click the Run button. The pipe wil run and previews will load.

Build Pipe: Convert Run


Now, let’s add a Filter Operation step by, again, clicking the ’+’ button after the Convert step, adding a filter command and clicking the “Save” button:

filter where: strpart(birthday, "/", 3) = "1980"

Build Pipe: Filter


Finally, let’s say that we want to add a Sort Operation in between the Convert and the Filter. To do so, click the ’+’ button between the two steps and then enter the Sort command below and save your changes:

sort col: state, city

Build Pipe: Sort

Now, we’ll click the Run button to refresh the pipe and the previews.


Edit or delete a step

You may review, edit or delete a step at any time.

To edit a step, simply click in the command you wish to edit, make your edits and then click Save Changes button to save your step.

To delete a step, hover over the step tile and click the ‘X’ to delete the step.

Build Pipe: Sort


Running a pipe

Pipe steps are saved by clicking the “Save” button next to any new or edited command. To run the pipe, select the 'Run’ button. This will refresh the entire pipe, including previews and output.


Scheduling a pipe

To schedule a pipe, go to the Pipes tab of your Project. Click the drop-down menu on the right and select Schedule. The scheduler will appear.

Schedule Pipe

Select the time and frequency. To automatically schedule, toggle the from 'Not Scheduled’ to 'Scheduled’. This may also be turned on and off directly from the pipe tab. The scheduling toggle is at the right.

Sharing and embedding a pipe

To embed a pipe in a website via iframe, go to the Pipes tab of your Project. Click the drop-down menu on the right and select Share. A dialog will appear with an iframe link. Copy and paste this like like you would with any other iframe embed (e.g., youtube)

Deleting a pipe

To delete a pipe, go to the Pipes tab of your Project. Click the drop-down menu on the right and select Move to Trash.

The pipe will be removed from your pipe list and placed into Trash. To view the pipe, click the Trash tab, where you can opt to restore your pipe or permanently delete it.

Connections

Pipe processing is only as good as the input and output. Connections are any inputs or outputs that require authentication. The Connections area is used for managing these connections specific to a Project.

Connections List

Connections may be added to a Project and used by any Member of that Project, thereby letting colleagues share connections without sharing credentials.

Adding a new connection

To add a new Connection to your Project, click on the Connection tab on the left. Once in the Connection section, click on the “New Connection” button at the top-right of the content area. A connection chooser will appear:

Choose Connection

Select the connection you wish to authenticate and authentication instructions will appear.

Add Connection

You can edit the following:

Updating or deleting a connection

To edit or delete a connection, click the down arrow on the right-side of the connection to display the menu.

Add Connection

The menu offers the following options:

Members

Data projects are a team sport, so Flex.io was built with collaboration and sharing in mind. The Members area, within a Project, is used for managing your team.

Members List

Adding colleagues to your project provide some advantages, including:

Member permissions

Adding a new member

To add a new Member to your Project, click on the Member tab on the left. Once in the Member section, click on the “Add Member” button at the top-right of the content area.

Members Invitation

Enter the email address used when the Member signed up for their Flex.io account. Multiple members can be invited at the same time by separating the email addresses with commas. You may also add an optional message. Click the “Send Invites” button and an email will be sent to the Member alerting them that they have been added to your Project.

Removing a member

To remove a member from your Project, click on the “x” button at the right-side of their name.

Command bar operations

A command is used every time you create a pipe step. For instance, if you want to filter some records in a table, you would use a filter command. If you create a script, you use an execute command to initiate the step. If you add an input or output from the pipe overview, you are simply using the GUI to insert an input and output command. Commands can be entered in the Command Bar of the Pipe Builder to create a new step in your pipe.

Using the command bar

TODO; show how command bar works w/ hints, save/cancel, etc.

Operations

Flex.io currently provides the following operations that can be utilized in the Command Bar:

Command Description
calc Add a calculated field or a calculated object
convert Convert files from one format into another
email Send an email notification
execute Execute a script
filter Filter a table, set of objects, or the file list based on a filter expression
input Input files or data sets
limit Limit the number or rows or objects
merge Combine two or more files
output Output files or data sets
rename Rename columns or file names
select Select a set of columns
settype Set field type for tabluar data
sort Sort a table, set of objects, or the file list
transform Transform and standardize file content

TODO; SYNTAX RULES (values in single quotes, parentheses for expressions and brackets for spaces in field names, boolean operators, other escape characters, numeric operators (e.g., + - / != , etc), => rename, etc.


Calc

Add a calculated field to a table or a calculated object to JSON/XML objects. If “type” is not specified, a data type is automatically determined based on the results of the calculation expression.

Syntax
calc name: OutputName formula: CalculationExpression type: DataType decimal: Scale
Parameters
Parameter Values Required Description
name [value] Yes Name of the calculated field
formula [expression] Yes Expression used in calculating the values of the calculated field
type text, numeric, integer, date, datetime, boolean Yes Type of the calculated field
decimal [number (0 - 8)] No For numeric fields, precision - the number of decimal places
Basic Usage

Create a new field called ‘upper_city’ that makes all characters in the field 'city’ uppercase.

calc name: upper_city type: text formula: upper(city)

Create a new field called “discount” that calculates the difference between 'listprice’ and 'salesprice’ fields.

calc name: discount type: numeric decimal: 2 formula: listprice - saleprice

Create a new field called 'pct_discount’ that calculates the difference between “listprice” and “sales price” fields as a percentage discount

calc name: discount type: numeric decimal: 2 formula: (listprice - [sale price])/listprice * 100

Create a new field called 'check’ that returns true when the 'flag’ field includes an 'X’

calc name: review type: boolean formula: contains(flag, 'X')

Convert

Convert files from one format into another. Format options for both input and output include: delimited, json, pdf, table.

Syntax
convert from: SourceFormatType to: TargetFormatType delimiter: DelimiterCharacter qualifier: QualifierCharacter header: HeaderRow
Parameters
Parameter Values Required Description
from delimited, json, pdf, table, text - Format to convert from; if no value provided will attempt to sense input format
to delimited, json, pdf, table, text - Format to convert to; if no value provided default = table
delimiter comma, none, pipe, semicolon, tab, "custom" - Delimiter type; if no value provided default = comma
qualifier none, single-quote, double-quote, "custom" - Text qualifier type
header true, false - Specifies if first row should be treated as a header row with field names
Basic Usage

Convert a CSV file qualified by double-quotation marks with header row to a table

convert from: delimited to: table delimiter: comma qualifier: double-quote header: true

Convert a PDF file to text

convert from: pdf to: text

Convert a Table to JSON

convert from: table to: json

Email

Send an email notification, either within a pipe or as an output of the pipe.

Syntax
email to: EmailTo from: EmailFrom subject: Subject body: MessageText html: MessageHTML reply-to: EmailReply data: DataStreamType
Parameters
Parameter Values Required Description
to [email address] - Email-to address
from [email address] - Email-from address
subject [text] - Subject line
body [text] - Message, plain text
html [text] - Message, html
reply-to [email address] - Email reply-to address
data none, body, attachment - Handling of data stream; none, in the body itself or as an attachment to the email
Basic Usage

Email a message and include the output data stream as an attachment

email to: you@flex.io from: me@flex.io subject: Cats! body: I like cats, see attached. data: attachment

Email a notification message after a step runs

email to: you@flex.io from: me@flex.io subject: This is a pipe. body: See pipe run. Run, pipe, run. data: none

Execute

Execute a programmatic script and apply the operations in the script to files in the input list. Users can specify the language as well as parameters to pass to the script that can be used during execution.

Syntax
execute lang: LanguageType code: [entered via code window]
Parameters
Parameter Values Required Description
lang python - Language to use
code [n/a] - Program logic to execute; entered via code window
Basic Usage

Add python script

execute lang: python code: [entered via code window]


[CODE WINDOW]:

import PIL
from PIL import Image
from io import BytesIO

def flexio_handler(input,output):
  output.content_type = input.content_type
  content = input.stream.buffer.read()
  output.stream.buffer.write(content.lower())

Filter

Filter a table, set of objects, or the file list based on a filter expression. Using the “file” parameter, a user can limit the files that are included in the filter operation to a specfic set. If the “file” parameter references a file attribute (e.g. “filename”, “filetype”, etc.), the filter operation is applied to the file list. The “exclude” parameter excludes the rows, objects, or files specified by the filter expression rather than selecting them.

Syntax
filter where: FilterExpression exclude: true
Parameters
Parameter Values Required Description
where [expression] - The filter condition
exclude true, false - when true, ouput the inverse of the filter condition
Basic Usage

Filter table to identify records where the 'population’ field is greater than 500,000

filter where: population > 500000

Filter table to identify all records where the 'state’ field is not equal to 'IL’, 'WI’ or 'MN’

filter where: state = 'IL' or state = 'WI' or state = 'MN' exclude: true

Input

Input files or data sets. Source is a required parameter that either specifies a source name, or generic connection type that can be used at runtime (e.g. upload, sftp, dropbox). Using the “connection” parameter, a user can specify a connection string for accessing an sftp site or a database without setting up a custom connection in the “Connections” area.

Syntax
input from: ConnectionType file: SourceName location: FilePath
Parameters
Parameter Values Required Description
from [alias] - The orign connection
file [name] - Filenames, tables, or objects; may utilize standard * and ? wildcards
location [path] - Base directory that applies to all file/path specifications made in file:
Basic Usage

Input a file from a URL

input file: https://raw.githubusercontent.com/foo/data.csv

Input files from multiple URLs

input file: https://raw.githubusercontent.com/foo/data1.csv, https://raw.githubusercontent.com/foo/data2.csv

Input files from multiple URLs and explicity name the files 'data_file1’ and 'data_file2’ for future use in the pipe

input file: https://raw.githubusercontent.com/foo/data1.csv => data_file1, https://raw.githubusercontent.com/foo/data2.csv => data_file2

Input a file from a connection, which has an Alias called 'my-connection’, and a file called cities.txt

input from: my-connection file: /docs/cities.txt

Input a feil from a connection, which has an Alias called 'my-connection’, and a file called cities.txt in the base directory

input from: my-connection location: /basedir file: cities.txt

Limit

Limit the number or rows or objects. If the “file” parameter is specified, this operation is only applied to the files included in the list.

Syntax
limit value: LimitNumber sample: SampleMethod
Parameters
Parameter Values Required Description
value [number (0 - 500)] - Number of lines/rows/records outputted
sample top, bottom, random - Returns a specified sample of records from the input
Basic Usage

Limit output to first 50 rows in the file:

limit value: 50

Limit output to bottom 50 rows in the file:

limit value: 50 sample: bottom

Limit output to random sample of 100 rows in the file:

limit value: 100 sample: random

Merge

Merge combines multiple tables or files together. Merge will attempt to reconcile structure automatically. If your inputs are both tables and text, Merge will convert everything to text and append together.

Syntax
merge
Parameters
Parameter Values Required Description
- - - N/A
Basic Usage

Merge all tables together and attempt to reconcile structure automatically

merge

Merge all text files and table together, convert everything to text and append

merge

Output

Output files or data sets. Target is a required parameter that either specifies an output connection name (i.e. a custom connection created in the “Connections” area), or generic connection type that can be used at runtime (e.g. download, sftp, etc.). Using the “connection” parameter, a user can specify a connection string for accessing an sftp site or a database without setting up a custom connection in the “Connections” area. An optional “zip” parameter can be used to create a Zip file output. An optional “as” parameter can be used to specify the names of files that are output to the destination target.

Syntax
output to: ConnectionType file: TargetName location: FilePath
Parameters
Parameter Values Required Description
to [alias] - The destination connection
file [name] - Streams to export to destination connection; default is to export all streams
location [path] - Base directory that applies to all destination files (e.g. target folder on Dropbox)
Basic Usage

Output all streams (e.g., if you are processing more than one file) to a single location with an Alias called 'my-connection’ and folder called 'myfolder’

output to: my-connection location: /myfolder

If you have three streams of data that you had input, 'data1’, 'data2’ and 'data3’; output 'data2’ to a single location with an Alias called 'my-connection’ and folder called 'myfolder’

output to: my-connection file: data2 location: /myfolder

Rename

Rename columns from existing name to a new name. Works in tables only.

Syntax
rename col: ColumnList=>NewColumnName file: FileName=>NewFileName
Parameters
Parameter Values Required Description
col [field] * For tables, the columns selected to rename
file [file name] * For files, rename for output

* - one of these parameters is required

Basic Usage

Rename the field 'no’ to 'number’

rename col: no=>number

Rename the field 'no to 'number’ and the field 'inv’ to 'invoice’

rename col: no=>number, inv=>invoice

Rename the file 'file123 to 'fileabc’ and the file 'file456’ to 'filedef’

rename file: file123=>fileabc, file456=>filedef

Select

Select a set of columns, objects, or files based on their name or index position.

Syntax
select col: ColumnList file: FileName
Parameters
Parameter Values Required Description
col [field] * For tables, the columns selected for output
file [file name or extension] * For files, the names selected for output

* - one of these parameters is required

Basic Usage

From the full table, select only these fields: 'first_name’, 'last_name’, 'city’, 'state’, 'zip’, 'country’

select col: first_name, last_name, city, state, zip, country

Select 'file1.txt’ and 'file2.txt’ from these inputs: 'file1.txt’, 'file2.txt’, 'file3.csv’

select file: file1.txt, file2.txt

Select all .txt files from these inputs: 'file1.txt’, 'file2.txt’, 'file3.csv’

select file: *.txt

Select all any files that begin with the name 'file’ and are extension .txt from these inputs: 'file1.txt’, 'file2.txt’, 'file3.csv’

select file: file*.txt

Settype

Set the field type for columns in tabluar data

Syntax
settype col: ColumnList type: DataType decimal: Scale
Parameters
Parameter Values Required Description
col [field] Yes For tables, the columns selected for output
type text, numeric, integer, date, datetime, boolean Yes Type of the calculated field
decimal [number (0 - 8)] No For numeric fields, precision - the number of decimal places
Basic Usage

Change the type of a field 'birthday’ from text type to date type

settype col: birthday type: date

Change the type of a field 'amount’ from text type to numeric type with two decimals

settype col: amount type: numeric decimal: 2

Change the type of the fields 'flag1’, 'flag2’, 'flag3’ and 'flag4’ from text type to boolean type

settype col: flag1, flag2, flag3, flag4 type: boolean

Change the type of the field 'zip_code’ from numeric type to text type

settype col: zip_code type: text

Sort

Sort a table, set of objects, or the file list. If no parameters are included, all content is sorted in ascending order. Otherwise, you can specify the sort order by adding an ascending or descending tag to a list of columns or objects.

Syntax
sort col: ColumnList
Parameters
Parameter Values Required Description
col [field] [ascending, descending] - List of the columns to sort on and the sort order
Basic Usage

Sort in ascending order by the 'lastname’ and 'firstname’ columns:

sort col: lastname, firstname

Sort in descending order by 'lastname’ and then ascending by 'firstname’:

sort col: lastname descending, firstname ascending

Transform

Transform and standardize the content of a table, set of objects, or files. This provides a simple, shorthand option for performing common transformation operations. If no set of columns or set of objects is specified, the transformation is applied to the entire data set.

Syntax
transform col: ColumnList case: CaseOption trim: TrimOption
Parameters
Parameter Values Required Description
col [field] - Columns to transform; default applies transform to all columns]
case none, lower, upper, proper, first-letter - Convert the specified columns to a different case
trim leading, trailing, leading-trailing - Trim spaces from the specified columns
Basic Usage

Change all text in a table into uppercase

transform case: upper

Change the text in the field 'firstname’ to uppercase

transform col: firstname case: upper

Remove leading and trailing spaces from the field 'firstname’

transform col: firstname trim: leading-trailing

Variables

When you run a pipe, sometimes it is useful to pass external parameters to the pipe in order to modify the pipe’s behavior. For instance, you may wish to change filter criteria or dynamically set an email address.

Variables can be used anywhere in the command bar, using the following format:

${variable-name}

For example, to pass a state value for filtering results, you would include the following Filter command:

filter where: state = '${state}'

To pass through a second variable for an amount, you would just add a second variable:

state = '${state}' and amount > ${amount}

To see an example of a simple web page that utilizes a variable to return results, please see this post on adding Dynamic Content to a Static Web Page

Functions and syntax

TODO; 1) what is a function; 2) which operations utilize functions

Function index

Below is a list of functions you may use in Flex.io. Each function name is linked to further detail with syntax and examples.

Conversion functions
Name Format Description
to_char to_char(mixed val, text format) Converts a date or numeric value to a character string
to_number to_number(mixed val, text format) Converts a character string to a numeric value
to_date to_date(mixed val, text format) Converts a character string to a date
to_timestamp to_timestamp(mixed val, text format) Adds a timestamp
String functions
Name Format Description
concat concat(mixed val, ...) TODO;
contains contains(mixed, text search_str) Determines if a search string is included in a string
left left(mixed val, integer count) Returns the leftmost characters in a string
length length(mixed val) Returns the number of characters in a string
lower lower(mixed val) Converts all characters in a string to lowercase
lpad lpad(mixed val, integer length [, text fill_chars]) Adds characters or spaces to the left side of a string
ltrim ltrim(mixed val [, text chars]) Removes characters or spaces from the left side of a character string
md5 md5(mixed val) TODO;
replace replace(mixed val, text from_str, text to_str) Replaces each occurrence of a search string with another string
reverse reverse(mixed val) Reverses the order of characters in a string
right right(mixed val, integer count) Returns the rightmost characters in a string
rpad rpad(mixed, integer length [, text fill_chars]) Adds characters or spaces to the right side of a character string
rtrim rtrim(mixed val [, text trim_chars]) Removes characters or spaces from the right side of a string
strpart strpart(mixed, text search_str, integer index) Returns the nth section of an input string
strpos strpos(mixed, text search_str) TODO;
substr substr(mixed, integer from [, integer count]) Returns a portion of an input string
trim trim(mixed val [, text chars]) Removes characters from the left and right sides of an input string
upper upper(mixed val) Converts all characters in a string to uppercase
Numeric functions
Name Format Description
acos acos(number val) Calculates the arc cosine of a number
asin asin(number val) Calculates the arc sine of a number
atan atan(number val) Calculates the arc tangent of a number
abs abs(number val) Calculates the absolute value of a number
ceiling ceiling(number val) Calculates the smallest number greater than or equal to an input number
cos cos(number val) Calculates the cosine of a number
exp exp(number val) Calculates the constant E raised to a power of a number
floor floor(number val) Calculates the largest number less than or equal to an input number
ln ln(number val) Calculates the natural log of a number
log log(number val) Calculates the logarithm of a number in a specified base
mod mod(number val, number val) Calculates the remainder of the numerator divided by the denominator
pi pi() Returns an approximation of the number Pi
pow pow(number val, number val) Calculates the base raised to a power of a number
round round(number val) Rounds a number to a specified precision
sign sign(number val) Returns the sign of a number
sin sin(number val) Returns the sine of a number
tan tan(number val) Calculates the tangent of a number
trunc trunc(number val) Truncates a number to a specified precision
Date/Time functions
Name Format Description
current_date current_date() Returns the current system date
day day(date val) Returns a day from an input date
hour hour(date val) Returns an hour from an input date
minute minute(date val) Returns a minute from an input date
month month(date val) Returns a month from an input date
now now() TODO;
second second(date val) Returns a second from an input date
year year(date val) Returns a year from an input date
Conditional functions
Name Format Description
if if(boolean val, mixed val, mixed val) Returns one of two values based on a Boolean statement
isnull isnull() Determines if a field has a null or empty value

Rules for creating expressions

To properly interpret an expression or formula, Flex.io utilizes the following precedence rules:

Precedence Operator Associativity Description
1 () left-to-right grouping
2 + - NOT (!) right-to-left unary plus and minus, logical NOT
3 * / % left-to-right multiplication, division, modulo
4 + - left-to-right addition and subtraction
5 < <= > >= left-to-right comparison
6 = != (<>) left-to-right equal and not-equal (!= and <> are equivalent)
7 AND left-to-right logical conjunction
8 OR left-to-right logical disjunction

Conversion functions

Conversion functions are used in converting one type of data to another type (e.g., converting a character type to a numeric type).

Name Format Description
to_char to_char(mixed val, text format) Converts a date or numeric value to a character string
to_number to_number(mixed val, text format) Converts a character string to a numeric value
to_date to_date(mixed val, text format) Converts a character string to a date
to_timestamp to_timestamp(mixed val, text format) Adds a timestamp

to_char

Syntax

The function to_char() converts a date or numeric value to a character string

to_char(mixed val, text format)
Examples
to_char(10.5) = '11'
to_char(1.23456789, 2) = '1.23'
to_char(5.555555, 2) = '5.56'
to_char(DATE(2002, 12, 25)) = '20021225'
to_char(DATE("11/05/2002")) = '20021105'

to_date

The function to_date() converts a character string to a date.

Syntax
to_date(mixed val, text format)
Examples
to_date(2007,12,25) = the date, December 25, 2007
to_date(2007,12,25,8,15,30) = the date, December 25, 2007 at 8:15:30 A.M.
to_date('07/04/2007') = the date, July 4, 2007
to_date('2007-10-22') = the date, October 22, 2007
to_date(0,0,0) +  2454917 = the date, March, 26, 2009
to_date(1238088021 * 1000) = the date, March, 26, 2009
to_date('20071122','YYYYMMDD') = 11/22/2007
to_date('2007-22-11','YYYYDDMM') = 11/22/2007
to_date('11222007','MM/DD/YYYY') = 11/22/2007 
to_date('11/22/2007', 'MMDDYYYY') = 11/22/2007

to_number

The function to_number() converts a character string to a numeric value.

Syntax
to_number(mixed val, text format)
Examples
to_number('12.085') = 12.085
to_number('-10') = -10
to_number('5548A') = 5548
to_number('5548A-12345') = 5548
to_number('A-5548-12345') = 0

to_timestamp

The function to_timestamp() adds a timestamp.

Syntax
to_timestamp(mixed val, text format)
Examples
TODO;

String functions

String functions are used TODO;

Name Format Description
concat concat(mixed val, ...) TODO;
contains contains(mixed, text search_str) Determines if a search string is included in a string
left left(mixed val, integer count) Returns the leftmost characters in a string
length length(mixed val) Returns the number of characters in a string
lower lower(mixed val) Converts all characters in a string to lowercase
lpad lpad(mixed val, integer length [, text fill_chars]) Adds characters or spaces to the left side of a string
ltrim ltrim(mixed val [, text chars]) Removes characters or spaces from the left side of a character string
md5 md5(mixed val) TODO;
replace replace(mixed val, text from_str, text to_str) Replaces each occurrence of a search string with another string
reverse reverse(mixed val) Reverses the order of characters in a string
right right(mixed val, integer count) Returns the rightmost characters in a string
rpad rpad(mixed, integer length [, text fill_chars]) Adds characters or spaces to the right side of a character string
rtrim rtrim(mixed val [, text trim_chars]) Removes characters or spaces from the right side of a string
strpart strpart(mixed, text search_str, integer index) Returns the nth section of an input string
strpos strpos(mixed, text search_str) TODO;
substr substr(mixed, integer from [, integer count]) Returns a portion of an input string
trim trim(mixed val [, text chars]) Removes characters from the left and right sides of an input string
upper upper(mixed val) Converts all characters in a string to uppercase

concat

The function concat() connects two or more strings together.

Syntax
concat(mixed val, ...)
Examples
concat('I like','apples.') = I likeapples.
concat('I like',' ','apples.') = I like apples.
concat('I like ',2,' eat apples.') = I like 2 eat apples.

contains

The function contains() compares two strings of text and returns TRUE if the first string contains the second string. If not, returns FALSE.

Syntax
contains(string, search string)
Examples
contains('Green apples taste better than red apples.', 'apples') = TRUE
contains('Green apples taste better than red apples.', 'oranges') = FALSE"

left

The function left() returns the leftmost characters in a string.

Syntax
left(mixed val, integer count)
Examples
left('APPLES AND ORANGES', 5) = 'APPLE' 
left('APPLES AND ORANGES', 10) = 'APPLES AND' 
left('APPLES AND ORANGES', 20) = 'APPLES AND ORANGES'

length

The function length() returns the number of characters in a string.

Syntax
length(mixed val)
Examples
length('APPLES') = 6 
length(' APPLES ') = 8
length(TRIM(' APPLES ')) = 6

lower

The function lower() converts all characters in a string to lowercase.

Syntax
lower(mixed val)
Examples
lower('APPLES') = 'apples'
lower('Apples and Oranges') = 'apples and oranges'

lpad

The function lpad() adds characters or spaces to the left side of a character string.

Syntax
lpad(mixed val, integer length [, text fill_chars])
Examples
lpad('APPLES', 10, '.') = '....APPLES'
lpad('APPLES', 10) ='    APPLES'
lpad('APPLES', 3) = 'APP'

ltrim

The function ltrim() removes characters or spaces from the left side of a character string.

Syntax
ltrim(mixed val [, text chars])
Examples
ltrim(' APPLES ') = 'APPLES '
ltrim('APPLES', 'A') = 'PPLES'
ltrim('APPLES', 'AP') = 'LES'
ltrim('APPLES', 'P') = 'APPLES'
ltrim('APPLES', 'PA') = 'LES'
ltrim('APPLES', 'APS') = 'LES'
ltrim('ABRACADABRA', 'ABRC') = 'DABRA'
ltrim('ABRACADABRA', 'ABRCD') = ''

md5

The function md5() calculates the md5 hash value of the parameter passed to the function and returns it as a string.

Syntax
md5(mixed val)
Examples
md5('The quick brown fox jumps over the lazy dog.') = e4d909c290d0fb1ca068ffaddf22cbd0

regexp_replace

The function regex_replace() is a text-matching function that can be used three ways.

  1. It can be used to match and return a portion of a string that matches a regular expression specified in the second parameter of the function.
  2. It can be used to return a sub-portion of a string matching a regular expression, referencing the part of the regular expression enclosed in parentheses with a third, numeric parameter.
  3. It can be used to return a new string that replaces parts of the original string matching a regular expression with new values, specified by a third, string parameter.
Syntax
regexp_replace(mixed val, text pattern_str, text replace_str [, text flags])

(flags are i for case-insensitive match and g for global replace)
Examples
regexp_replace('2.71828 IS LESS THAN 3.14159', '[0-9.]+') = '2.71828'
regexp_replace('2.71828 IS LESS THAN 3.14159', '([0-9.]+)([^0-9.]+)([0-9.]+)', 1) = '2.71828'
regexp_replace('2.71828 IS LESS THAN 3.14159', '([0-9.]+)([^0-9.]+)([0-9.]+)', 3) = '3.14159'
regexp_replace('2.71828 IS LESS THAN 3.14159', '([0-9.]+)([^0-9.]+)([0-9.]+)', '$3 IS CALLED 'PI') = '3.14159 IS CALLED 'PI''

replace

The function replace() replaces each occurrence of a search string in an input string with another string.

Syntax
replace(mixed val, text from_str, text to_str)
Examples
replace('APPLES AND ORANGES', 'ORANGES', 'APPLES') = 'APPLES AND APPLES'
replace('APPLES AND ORANGES', 'APPLES', 'ORANGES') = 'ORANGES AND ORANGES'
replace('APPLES AND ORANGES', 'ES', 'E') = 'APPLE AND ORANGE'

reverse

The function reverse() reverses the order of characters in a string.

Syntax
reverse(mixed val)
Examples
reverse('APPLES') = 'SELPPA'
reverse('ORANGES') = 'SEGNARO'

Syntax

The function right() returns the rightmost characters in a string.

right(mixed val, integer count)
Examples
right('APPLES AND ORANGES', 7) = 'ORANGES'
right('APPLES AND ORANGES', 11) = 'AND ORANGES'
right('APPLES AND ORANGES', 20) = 'APPLES AND ORANGES'

rpad

The function rpad() adds characters or spaces to the right side of a character string.

Syntax
rpad(mixed, integer length [, text fill_chars])
Examples
rpad('APPLES', 10, '.') = 'APPLES....' 
rpad('APPLES', 10) = 'APPLES '
rpad('APPLES', 3) = 'APP'

rtrim

The function rtrim() removes characters or spaces from the right side of a string.

Syntax
rtrim(mixed val [, text trim_chars])
Examples
rtrim(' APPLES ') = ' APPLES'
rtrim('APPLES', 'S') = 'APPLE'
rtrim('APPLES', 'SE') = 'APPL'
rtrim('APPLES', 'E') = 'APPLES'
rtrim('APPLES', 'ES') = 'APPL'
rtrim('APPLES', 'ELP') = 'APPLES'
rtrim('APPLES', 'ELPS') = 'A'
rtrim('ABRACADABRA', 'ARBD') = 'ABRAC'
rtrim('ABRACADABRA', 'ARBDC') = ''

strpart

The function strpart() returns the nth section of an input string.

Syntax
strpart(mixed, text search_str, integer index)
Examples
strpart('12/25/2002','/',1) = '12'
strpart('12/25/2002','/',2) = '25'
strpart('12/25/2002','/',3) = '2002'
strpart('APPLES AND ORANGES',' ',1) = 'APPLES'
strpart('APPLES AND ORANGES',' ',2) = 'AND'
strpart('APPLES AND ORANGES',' ',3) = 'ORANGES'
strpart('APPLES, AND ORANGES','S, ',1) = 'APPLE'
strpart('APPLES, AND ORANGES','S, ',2) = 'AND'
strpart('APPLES, AND ORANGES','S, ',3) = 'ORANGE'

strpos

The function strpos() returns the location of a specified substring.

Syntax
strpos(mixed, text search_str)
Examples
strpos('high', 'ig') = 2
strpos('high', 'xyz') = 0

substr

The function substr() returns a portion of an input string.

Syntax
substr(mixed, integer from [, integer count])
Examples
substr('APPLES AND ORANGES', 1, 6) = 'APPLES'
substr('APPLES AND ORANGES', 8, 3) = 'AND'
substr('APPLES AND ORANGES', 12) = 'ORANGES'


trim

The function trim() removes characters from the left and right sides of an input string.

Syntax
trim(mixed val [, text chars])
Examples
trim(' APPLES ') = 'APPLES'
trim('APPLES', 'AS') = 'PPLE'
trim('APPLES', 'APS') = 'LE'
trim('APPLES', 'E') = 'APPLES'
trim('APPLES', 'APS') = 'LE'
trim('APPLES', 'PSA') = 'LE'
trim('APPLES', 'PS') = 'APPLE'
trim('ABRACADABRA', 'ABR') = 'CAD'
trim('ABRACADABRA', 'ARBD') = 'C'

upper

The function upper() converts all characters in a string to uppercase.

Syntax
upper(mixed val)
Examples
upper('apples') = 'APPLES'
upper('Apples and Oranges') = 'APPLES AND ORANGES'

Numeric functions

Numeric functions are used TODO;

Name Format Description
acos acos(number val) Calculates the arc cosine of a number
asin asin(number val) Calculates the arc sine of a number
atan atan(number val) Calculates the arc tangent of a number
abs abs(number val) Calculates the absolute value of a number
ceiling ceiling(number val) Calculates the smallest number greater than or equal to an input number
cos cos(number val) Calculates the cosine of a number
exp exp(number val) Calculates the constant E raised to a power of a number
floor floor(number val) Calculates the largest number less than or equal to an input number
ln ln(number val) Calculates the natural log of a number
log log(number val) Calculates the logarithm of a number in a specified base
mod mod(number val, number val) Calculates the remainder of the numerator divided by the denominator
pi pi() Returns an approximation of the number Pi
pow pow(number val, number val) Calculates the base raised to a power of a number
round round(number val) Rounds a number to a specified precision
sign sign(number val) Returns the sign of a number
sin sin(number val) Returns the sine of a number
tan tan(number val) Calculates the tangent of a number
trunc trunc(number val) Truncates a number to a specified precision

abs

The function abs()* calculates the absolute value of a number.

Syntax
abs(number val)
Examples
abs(-42) = 42 \nabs(0) = 0
abs(33) = 33

acos

The function acos()calculates the arc cosine of a number.

Syntax
acos(number val)
Examples
acos(0) = 1.5708
acos(COS(0)) = 0
acos(COS(1)) = 1
acos(COS(PI())) = 3.1416

asin

The function asin() calculates the arc sine of a number.

Syntax
asin(number val)
Examples
asin(0) = 0
asin(SIN(1)) = 1
asin(SIN(PI())) = 0
asin(SIN(PI()/2)) = 1.5708

atan

The function atan() calculates the arc tangent of a number.

Syntax
atan(number val)
Examples
atan(0) = 0
atan(tan(1)) = 1
atan(pi()) = 1.2626

ceiling

The function ceiling() calculates the smallest number greater than or equal to an input number.

Syntax
ceiling(number val)
Examples
ceiling(10.333) = 11
ceiling(-10.333) = -10
ceiling(10.333, 2) = 10.34
ceiling(-10.333, 2) = -10.33
ceiling(1024, -2) = 1100

cos

The function cos() calculates the cosine of a number.

Syntax
cos(number val)
Examples
cos(0) = 1
cos(PI()) = -1
cos(PI()/2) = 0
cos(100) = 0.8623

exp

The function exp() calculates the constant E raised to a power of a number.

Syntax
exp(number val)
Examples
exp(-1) = 0.3679
exp(0) = 1.0000
exp(3) = 20.0855
exp(10) = 22026.4658

floor

The function floor() calculates the largest number less than or equal to an input number.

Syntax
floor(number val)
Examples
floor(10.333) = 10
floor(-10.333) = -11
floor(10.333, 2) = 10.33
floor(-10.333, 2) = -10.34
floor(1024, -2) = 1000

ln

The function ln() calculates the natural log of a number.

Syntax
ln(number val)
Examples
ln(1) = 0
ln(10) = 2.3026
ln(EXP(1)) = 1
ln(EXP(10)) = 10

log

The function log() calculates the logarithm of a number in a specified base.

Syntax
log(number val)
Examples
log(10) = 1
log(10, 2) = 3.3219
log(1024, 2) = 10
log(1, 0) = 0

mod

The function mod() calculates the remainder of the numerator divided by the denominator.

Syntax
mod(number val, number val)
Examples
mod(9, 3) = 0
mod(10, 3) = 1
mod(-10, 3) = -1
mod(10.2, 3) = 1.2

pi

The function pi() returns an approximation of the number Pi.

Syntax
pi()
Examples
pi() = 3.14159265

pow

The function pow() calculates the base raised to a power of a number.

Syntax
pow(number val, number val)
Examples
pow(3, 2) = 9
pow(9, 0.5) = 3
pow(3, 0) = 1
pow(3, -2) = 0.1111

round

The function round() rounds a number to a specified precision.

Syntax
round(number val)
Examples
round(10.333) = 10
round(10.333, 2) = 10.330
round(10.666) = 11
round(10.666, 2) = 10.670
round(-10.333, 2) = -10.330
round(-10.666, 2) = -10.67


sign

The function sign() returns the sign of a number.

Syntax
sign(number val)
Examples
sign(-42) = -1
sign(0) = 0
sign(33) = 1

sin

The function sin() returns the sine of a number.

Syntax
sin(number val)
Examples
sin(0) = 0
sin(PI()) = 0
sin(PI()/2) = 1
sin(100) = -0.5064

tan

The function tan() calculates the tangent of a number.

Syntax
tan(number val)
Examples
tan(0) = 0
tan(PI()) = 0
tan(COS(PI())) = -1.5574
tan(100) = -0.5872

trunc

The function upper() truncates a number to a specified precision.

Syntax
trunc(number val)
Examples
trunc(10.333) = 10
trunc(10.333, 2) = 10.330
trunc(10.666) = 10
trunc(10.666, 2) = 10.660
trunc(-10.333) = -10
trunc(-10.666) = -10

Date/Time functions

Date/Time functions are used TODO;

Name Format Description
current_date current_date() Returns the current system date
day day(date val) Returns a day from an input date
hour hour(date val) Returns an hour from an input date
minute minute(date val) Returns a minute from an input date
month month(date val) Returns a month from an input date
now now() TODO;
second second(date val) Returns a second from an input date
year year(date val) Returns a year from an input date

current_date

The function current_date() returns the current system date.

Syntax
current_date()
Examples
current_date() = 2015-05-25

(if we were using this function on May 25, 2015)

day

The function day() returns a day from an input date.

Syntax
day(date val)
Examples
day(to_date('11/22/2007')) = 22
day(to_date(2007, 12, 25)) = 25

hour

The function hour() returns an hour from an input date.

Syntax
hour(date val)
Examples
hour(to_date(2007, 07, 04, 12, 30, 22)) = 12
hour(to_date(2007, 12, 25, 8, 10, 20)) = 8

minute

The function minute() returns a minute from an input date.

Syntax
minute(date val)
Examples
minute(to_date(2007, 07, 04, 12, 30, 22)) = 30
minute(to_date(2007, 12, 25, 8, 10, 20)) = 10

month

The function month() returns a month from an input date.

Syntax
month(date val)
Examples
month(to_date('11/22/2007')) = 11
month(to_date(2007, 7, 4)) = 7

now

The function now() returns the current system date and time.

Syntax
now()
Examples
now() = 2015-05-25 16:09:43.130029

(if we were using this function on May 25, 2015 at 16:09:43.130029 UTC)

second

The function second() returns a second from an input date.

Syntax
second(date val)
Examples
second(to_date(2007, 07, 04, 12, 30, 22)) = 22
second(to_date(2007, 12, 25, 8, 10, 20)) = 20

year

The function year() returns a year from an input date.

Syntax
year(date val)
Examples
year(to_date('11/03/2007')) = 2007
year(to_date(2007, 12, 25)) = 2007

Conditional functions

Conditional functions are used TODO;

Name Format Description
if if(boolean val, mixed val, mixed val) Returns one of two values based on a Boolean statement
isnull isnull() Determines if a field has a null or empty value

if

The function if() returns one of two values based on a Boolean statement.

Syntax
if(boolean val, mixed val, mixed val)
Examples
if(1 + 1 = 2, 'correct', 'incorrect') = 'correct'
if(1 + 1 = 3, 'correct', 'incorrect') = 'incorrect'
if('A' + 'B' = 'AB', 1, -1) = 1
if('A' + 'B' <> 'AB', 1, -1) = -1

isnull

The function isnull() determines if a field has a null or empty value.

Syntax
isnull()
Examples
isnull('123') = FALSE
isnull('abc') = FALSE
isnull(null) = TRUE

Account management

You can manage your Flex.io Account and preferences by clicking on “Account” in the menu under your profile image in the header at the top-right of your screen.

Account Navigation

Your Account page will open, with options to edit profile, region and password settings as well as generate unique API keys.

Editing profile information

Select the Profile tab to edit your profile information.

Account Profile

You may edit your first name and last name; at this time you cannot change your username or email address associated with your account.

Editing your regional settings

Select the Region tab to edit your regional settings.

Account Regional Settings

You may change your time zone here, which is useful to set for scheduling Pipes. Use the search functionality to find a city near you.

Creating an API key

Select the API tab to generate an API key.

Account API Key

API keys are used with both the API as well as the Command Line Interface (CLI). You may generate any number of unique keys. To delete a key, hover over the key and select the X at the right.

Changing a password

Select the Password tab to change your password.

Account Password

First enter your current password; then enter your new password once and then a second time to confirm. Select the Save Changes button to confirm.

Getting further help

Flex.io is currently in beta, so while we whip everything into tip top shape, you’ll no doubt encounter some bugs and missing features.

But! Good news. We’re more than happy to lend a hand, either via email, phone or webcast.

Please shoot us a note at support@flex.io and we’ll hop to it! Thanks!

 

 

 

 

 

©2017 All Rights Reserved. Flex.io™ is a trademark of Gold Prairie LLC.