Expression Library

Description

An expression is a basic function that does a conversion, calculation, cast to another data type, or other action on data in a column or in a dashboard chart object. Examples are startswith, max, or current_date. PlaidCloud expressions are based on PostgreSQL. For a more in depth tutorial or reference guide, please see: tutorial

There are three primary areas to apply expressions - metrics and calculated columns in datasets, and chart objects in dashboards.

Navigating to a dataset

In order to view and edit metrics and calculated expressions:

1. Sign into plaidcloud.com and navigate to Dasboards. Select the dashboard you want to work in.
2. Select Data > Datasets from the menu.
3. Search for a dataset to view or modify
4. Hover over the dataset with the cursor and you will see icons in the actions column.
5. Click the edit icon beneath Actions

Viewing a chart object and adding an expression

You can add expressions to chart objects on a dashboard. For example, if you want to add an expression to a table object (a calculated column), you can:

1. Open the chart object by opening a dashboard, clicking on the three dot icon, and selecting "View chart in Explore".
2. Now that you are editing the chart, you can add a new Dimension or Metric, and do a SIMPLE expression, or a CUSTOM SQL expression

Now that you have located where you want to add an expression, you can use the table below as a guide to determining what expression you are looking for.

Data Types

There are a wide variety of standard data types (dtypes) to support your requirements. As datasets become larger, determining smaller size dtypes for value storage can shrink the size of the table and improve performance. The following dtypes are available:

• Boolean
• Text
• Numbers
  • SmallFloat (6 Digits)
  • Float (15 Digits)
  • BigFloat
  • SmallInteger (16 bit) (-32768 to 32767)
  • Integer (32 bit) (-2147483648 to 2147483647)
  • BigInteger (64 bit) (-9223372036854775808 to 9223372036854775807)
  • Numeric
• Dates and Times
  • Date
  • Timestamp
  • Time Interval

You can convert from one dtype to another using the func.cast() process.

Case Examples

A simple example

This example returns a person's name. It starts off searching to see if the first name column has a value (the "if"). If there is a value, concatenate the first name with the last name and return it (the "then"). If there isn't a first name, then return the last name only (the "else").

case(
        (table.first_name.isnot(None), func.concat(table.first_name, table.last_name)), 
        else_ = table.last_name
    )

A more complex example with multiple conditions

This example returns a price based on quantity. "If" the quantity in the order is more than 100, then give the customer the special price. If it doesn't satisfy the first condition, go to the second. If the quantity is greater than 10 (11-100), then give the customer the bulk price. Otherwise give the customer the regular price.

case( 
        (order_table.qty > 100, item_table.specialprice), 
        (order_table.qty > 10, item_table.bulkprice) , 
        else_=item_table.regularprice
    )

This example returns the first initial of the person's first name. If the user's name is wendy, return W. Otherwise if the user's name is jack, return J. Otherwise return E.

case( 
        (users_table.name == "wendy", "W"), 
        (users_table.name == "jack", "J"), 
        else_='E'
    )

The above may also be written in shorthand as:

case(
    {"wendy": "W", "jack": "J"}, 
    value=users_table.name, 
    else_='E' 
)

Other Examples

In this example is from a Table:Lookup step where we are updating the "dock_final" column when the table1. dock_final value is Null.

case(
    (table1.dock_final == Null, table2.dock_final),
    else_ = table1.dock_final
    )

This example is from a Table:Lookup step where we are updating the "Marketing Channel" column when "Marketing Channel" in table1 is not 'none' or the "Serial Number" contains a '_'.

case(
    (get_column(table1, 'Marketing Channel') != 'none', get_column(table1, 'Marketing Channel')),
    (get_column(table1, 'Serial Number').contains('_'), get_column(table1, 'Marketing Channel')),
    (get_column(table2, 'Marketing Channel') != Null, get_column(table2, 'Marketing Channel')), 
    else_ = 'none'
    )

CASE WHEN "sol_otif_pod_missing" = 1 THEN
'POD is missing.'
ELSE
'POD exists.'
END

CASE WHEN
SUM("distance_dc_xd") = 0 THEN 0
ELSE
sum("XD")/sum("distance_dc_xd")
END

sum(CASE WHEN "dc" = 'ALAB' THEN
("sol_otif_infull" * "sol_otif_pgi_ontime")
ELSE
0.0
END) / sum(CASE WHEN "dc" = 'ALAB' THEN
1.0
ELSE
0.000001
END)

func.cast() type conversions

Other Examples cast(table.transaction_year, Numeric) cast(get_column(table, 'End_Date'),Text)

func.to() Data Type Conversions

Other Examples

to_char("Sales_Order_w_Status"."WeekName")

func.to_char(func.date_trunc('week', get_column(table, 'date_sol_delivery_required')), 'YYYY-MM-DD')

func.to_date(get_column(table, 'File Creation Date'), 'YYYYMMDD')

result.CreateDate<func.to_date('09022022', 'MMDDYYYY')

to_char("date_delivery", 'YYYY-mm-dd')

Other Date Time Examples

Date Trunc

func.date_trunc('week', get_column(table, 'Date' ))

func.to_char(func.date_trunc('week', get_column(table, 'date_sol_delivery_required')), 'YYYY-MM-DD')

func.to_char(func.date_trunc('week', ((table.Date) - 6)),'MON-DD')

The following patterns can be used to select specific parts of a timestamp or to format date/time as desired.

And Operator

Example 1

This example checks if the period is any of the three specified dates.

and_(  
    table.color == 'green',  
    table.shape == 'circle',  
    table.price >= 1.25  
)

Example 2

This example is checking if to ensure the origin_plant is not one of the values specified. This is using the != expression.

and_(  
    table.origin_plant != '5013',  
    table.origin_plant != '5026',  
    table.origin_plant != '5120',  
    table.origin_plant != '5287',  
    table.origin_plant != '5161',  
    table.origin_plant != '5192'  
)

Alternatively, for reference, the above check could be written using the not_ and or_ operators like this:

not_(  
    or_(  
        table.origin_plant == '5013',  
        table.origin_plant == '5026',  
        table.origin_plant == '5120',  
        table.origin_plant == '5287',  
        table.origin_plant == '5161',  
        table.origin_plant == '5192'  
    )  
)

Other Examples

and_(table.origin_plant != '5013',table.origin_plant != '5026')

Not Operator

not_(and_(table.VALUE_FC==0.0, table.VALUE_LC==0.0))

not_(or_(get_column(table, 'GL Account').startswith('7'), get_column(table, 'GL Account').startswith('8')))

Or Operator

Example 1 This example checks if the period is any of the three specified dates.

or_(  
    table.period == '2020_10',  
    table.period == '2020_11',  
    table.period == '2020_12'  
)

Example 2 This example is checking if order_reason_Include is null or has the word KEEP as a value.

or_(  
    table.order_reason_Include == 'KEEP',  
    table.order_reason_Include.is_(None)  
)

Coalesce Examples

func.coalesce(table.UOM,  'none', \n)

func.coalesce(get_column(table2, 'TECHNOLOGY_RATE'), 0.0)

func.coalesce(table_beta.adjusted_price, table_alpha.override_price, table_alpha.price) * table_beta.quantity_sold

Regexp Replace Examples

func.regexp_replace('plaidcloud', 'p', 'P') --> Plaidcloud

func.regexp_replace('remove12345alphabets','[[:alpha:]]','','g') --> 12345

func.regexp_replace('remove12345digits','[[:digit:]]','','g') --> removedigits

First Value Examples

This is an example of using the 'first_value()' capability to calculate the running time of the time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

table.timestamp - func.first_value(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp)

Adding the expression above to an Interval column called 'run_time' would result in an output table like this:

Lag Examples

This is an example of using the 'lag()' capability to calculate the time interval in time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

table.timestamp - func.lag(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp)

Adding the expression above to an Interval column called 'delta' would result in an output table like this:

Last Value Examples

This is an example of using the 'last_value()' capability to calculate the time remaining in time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

func.last_value(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp) - table.timestamp

Adding the expression above to an Interval column called 'remaining' would result in an output table like this:

Sum Examples

(sum("sol_otif_infull" * "sol_otif_pgi_ontime")) / (count(*) + 0.000001)

sum("sol_otif_qty_filled") / (sum("sol_otif_qty_requested") + 0.000001)

Count Examples

sum("RW")/COUNT(DISTINCT "ship_to_customer")

(sum("sol_otif_infull" * "sol_otif_pgi_ontime")) / (count(*) + 0.000001)

Array Examples

In the examples below, we will use the following table called contacts with the phones column defined with an array of text.

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY, 
  name VARCHAR (100), 
  phones TEXT []
);

The phones column is a one-dimensional array that holds various phone numbers that a contact may have.

To define multiple dimensional array, you add the square brackets.

For example, you can define a two-dimensional array as follows:

column_name data_type [][]

An example of inserting data into that table

INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);

or

INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-5843"}');

Array unnest

The unnest() function expands an array to a list of rows. For example, the following query expands all phone numbers of the phones array.

SELECT 
  name, 
  unnest(phones) 
FROM 
  contacts;

Output:

STRING_TO_ARRAY() function

This function is used to split a string into array elements using supplied delimiter and optional null string.

Syntax:
string_to_array(text, text [, text])

Return Type:
text[]

Example:

SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');

Output:
{xx,NULL,zz}