Versions Compared

Old Version 7

changes.mady.by.user Anthony George

Saved on

compared with

New Version Current

changes.mady.by.user Zoe Baldwin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue

Introduction

Enriching data is at the heart of analysis modelling from adding additional data and extrapolating , from looking up reference information to performing complex calculations and deduplication, PhixFlow covers it all. In this page we will highlight some of This page highlights the key areas of enrichment, provide providing examples, and list listing the links to additional useful enrichment resources.

PhixScript

Functions

PhixScript is the language of PhixFlow and it can be used anywhere that supports expressions, such as attributes on a table and in filters.

Single Function PhixScript

This is used in attribute expressions and filters alike where a single function is called.

Code Block
// If ExamResult is greater than 95 then return "Distinction" else return "Pass"
if( in.ExamResult > 95, "Distinction", "Pass")

Multiple Function PhixScript

PhixScripts containing multiple functions must be wrapped in a do(). do() can also be used within a function such as in if or switch. For more information see do().

The value returned from the PhixScript is the final value output, therefore it is useful to add the desired output to the end of the script.

Note that variables declared in an attribute expression (variables are declared with a $ symbol with the data type being implied) will be accessible to subsequent attributes within the same table using the $variable name.

Code Block
do(
	// calculate the miles per gallon.
	$mpg = in.distance / in.gallonsUsed,
	
	// if $mpg is over 65 set efficiency to Highly Efficient
	if( $mpg > 65, $efficiency = "Highly Efficient",
	
		// ELSE, set $efficiency to be null
		$efficiency = _NULL
	),	

	// Set the value returned by the PhixScript
	$efficiency
)


Excerpt
name_PhixScriptCheatSheet

Common Functions

There are over 115 functions available and these are listed in Functions

. but

, but to help you get started here is a short list of commonly used functions:

Common Functions

  1. Comments can be added to a single line using // or to a section using /* */.
  2. if: used where you need to evaluate a simple condition before processing an expression.
  3. ifNull: If its first value is provided then this is the returned value, else it will return the second value.
  4. switch: evaluates a set of conditions in turn, and returns the associated result for the first match.
  5. forEach: Iterates over an Array or Recordset and processes a script for each entry.
  6. listToString: takes a list and returns a string delimited by a specified value e.g. comma or pipe.
  7. split: Splits a string where a separator occurs, returning a list of value as an array..
  8. contains: Used on a string will
returneturns
  1. return true (1), if a string contains another specified string.
  2. listContains: Used on a list
r
  1. returns the position of
a
  1. an entry in a list (with 1 being the first item) if the value being looked for is found in the list, and false (0) otherwise.
  2. replaceAll: Replace all occurrences of a pattern with a replacement string. Also, see replaceFirst.
  3. dateDiff: Return the time difference between two dates in milliseconds.
  4. dateAdd: Add or subtract from a date using a specified unit e.g. _MONTH.
  5. now: Returns the current day and time, and today returns just the day.
  6. _NULL: is how to declare a null value e.g. $output = _NULL.
  7. substring: Returns the portion of the supplied string between specified character positions
, counting from 1
  1. .
  2. stringLength: Returns the length of a string.
  3. trim: Removes leading and trailing white spaces from a string, returning the cleansed string.
  4. toString: Converts a value from select data types into a string. For example, a date.
  5. toDate: Converts a string in a recognised date format (e.g. 20120521 or 20120521.172108) into a date.
  6. countElements: Returns the number of elements in an Array or Recordset.

Insert excerpt
Functions
Functions
name_Operators
nopaneltrue

Debugging

  • debug: Adds a debug message into the 
Log
  • System Console. This function is often used when creating a model and trying to work out why an Expression is not behaving as expected.
  • error: Adds
a
  • an error message into the 
Log

Writing PhixScript

Where a PhixScript is more than a single function, it must be wrapped in a do() function. do() can also be used within functions where you need to carryout multiple functions such as in if or switch.

Single Function PhixScript

This is used in attribute expressions and filters alike where a single function is called.

Code Block
// If ExamResult is greater than 95 then return "Distinction" else return "Pass"
if( in.ExamResult > 95, "Distinction", "Pass")

Multiple Function PhixScript

PhixScripts containing multiple functions must be wrapped in a do(). For more information see do().

The value returned from the PhixScript is the final value output, therefore it is useful to add the desired output to the end of the script.

Note that variables declared in an attribute expression (variables are declared with a $ symbol with the data type being implied) will be accessible to subsequent attributes within the same table by using the $variable name.

Code Block
do( // calculate the miles per gallon. $mpg = in.distance / in.gallonsUsed, // if $mpg is over 65 set efficiency to Highly Efficient if( $mpg > 65, $efficiency = "Highly Efficient", // ELSE, set $efficiency to be null $efficiency = _NULL ), // Set the value returned by the PhixScript $efficiency )

Variables

PhixFlow contains a number of Internal Variables available to PhixScript used for obtaining system and user information.

Variables in PhixFlow are declared using a

$ variable

$variable. The data type is implied by the first value entered, for example:

  1. $myArray  = []. Creates an array.
  2. $myString = "Hello World".
Create
  1. Creates a string.
  2. $myDate   = toDate("20120521").
Create
  1. Creates a date.
  2. $myNumber = 42.
Create a number
  1. Creates an integer.

Clearing Data

See Rollback Recordsets, for details on removing all or selected records from your Table. 

Lookup Information

Lookups can be performed using three different techniques, the method selected depends on your requirements and these are described below:

Lookup Function

See lookup for full details on the configuration.

The lookup function allows the user to pass variables to a lookup pipe. For example up, the pipe configuration for passing a variable would look like the following with the Type set to Lookup:

Image Removed

To retrieve the Region data from this lookup pipe, for example in an attribute expression, this would look like the following: lookup(in, $num = _out.MyValue).Region. For more information see Filtering and Sorting Data.

Lookup with Filtering

Scenario

Lookup information from a separate table and pass filtering filtered information to return only selected records. 

Example

You need to retrieve the region Region for a particular businesses. We will use the City to perform a lookup that will return a region.Image Removedbusiness using the City from its address.

Image Added

Solution

  1. You should have a two tables:
    • one with your data that will perform the lookup e.g. Business Data.
    • a second table with the data being looked up e.g. Region.
  2. Drag a pipe from the data to be looked up, to the data performing the lookup. In our case from Region to the Business Data.
  3. In the properties window that opens for the pipe:
    1. Set the name Name to be something short but meaningful.
    2. Set the Type to be lookup.
    3. In the filter section we will tell the lookup pipe we want to filter Region by City using the processed City city value from our Business Data.
      To use the processed version of city we use _out.City.
    4. We are passing data and not just a string value, so click to tell PhixFlow we are passing an expression.
    5. To use the processed version of City we use _out.City.
      6. The filter as followsThe filter will look like this:
      Image Modified
  4. The lookup is now setup, to access the Lookup its attributes, we open the Business Data table and add an attribute called Region.
    1. In the expression type pipe.attribute, in our case this will be rgn.Region.
    2. Save your changes.
  5. Run analysis on
    Insert excerpt
    _run_analysis
    _run_analysis
    nopaneltrue
     on Business Data and the Region will now populate.

Lookup with Order/Index

Scenario

This is a highly efficient method for performing a lookup against a small set of records. This kind of lookup will automatically cache a set amount of records, 3000 by default, in memory allowing for faster lookupsretrieval.

Example

You have a product Product ID on an invoice Invoice and want to return the name of the product to display. There are a finite amount of products so it is efficient to cache them. Image RemovedThe setup will look similar to:

Image Added

Solution

  1. You should have a two tables:
    • one with your data that will perform the lookup e.g.
    Invoice
    • Invoices.
    • a second table with the data being looked up e.g. Products.
  2. Drag a pipe from the data to be looked up, to the data performing the lookup. In our case from Product Products to the Invoice Invoices table.
  3. In the properties window that opens for the pipe:
    1. Set the name Name to be something short but meaningful.
    2. Set the Type to be lookup.
    3. Click 
      Insert excerpt
      _save
      _save
      nopaneltrue
      .
    4. In the Order/Index section, click 
      Insert excerpt
      _attributes_show
      _attributes_show
      nopaneltrue
      .
    5. Now drag the attributes from each table, onto the order index section, that will be used to in the lookup against one another.
      for For example from the Product Table drag UID and from the Invoice Table drag Product ID ProductID.
  4. The lookup is now setup, to access the Lookup its attributes, we open the Business Data and add an attribute called Region ProductName.
    1. In the expression type pipe.attribute, in our case this will be rgnpn.RegionProductName.
    2. Save your changes.
  5. Run analysis on Business Data Invoices and the Region product name will now populate.

Lookup Function

See lookup for full details on the configuration.

The lookup function allows the user to pass variables to a lookup pipe. For example up, the pipe configuration for passing a variable would look like the following with the Type set to Lookup:

Image Added

To retrieve the Region data from this lookup pipe, for example in an attribute expression, this would look like the following: lookup(in, $num = _out.MyValue).

Merges

Region

Merging Data

See Merging Tables for full details. This technique can also be used to deduplicate.

Matching

Simple exact matches and make reference to addition al functions for more advanced matching see Lev Distance etc.

Task Plan

Records

See Compare or Match Data for deduplicating and matching records.

More Information