Insert excerpt | ||||||||
---|---|---|---|---|---|---|---|---|
|
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
FunctionsPhixScript is the language of PhixFlow and it can be used anywhere that supports expressions, such as attributes on a table and in filters.
There are over 115 functions available and these are listed in Functions. but to help you get started here is a short list of commonly used functions:
- Comments can be added to a single line using
//
or to a section using/* */
. - if: used where you need to evaluate a simple condition before processing an expression.
- Syntax:
if(condition, trueExpression, falseExpression).
- Syntax:
- switch used where there are multiple conditions to evaluate and depending on the outcome
- forEach
- listToString
- contains
- listContains
- replaceAll
- dateDiff
- dateAdd
- now and today
- ifNull and _NULL
- substring
- stringLength
- trim
- toString
- toDate
Error Handling
Debug
error
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(). 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 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
) |
Excerpt | ||
---|---|---|
| ||
Common FunctionsThere are over 115 functions available and these are listed in Functions, but to help you get started here is a short list of commonly used functions:
|
Variables
make reference to variable.
Debugging
VariablesPhixFlow contains a number of Internal Variables available to PhixScript used for obtaining system and user information. Variables in PhixFlow are declared using a $variable. The data type is implied by the first value entered, for example:
|
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. For more information see Filtering and Sorting Data.
Lookup
Functionwith Filtering
Scenario
Lookup information from a separate table and pass filtering filtered information dynamically to return only selected records.
Example
You need to retrieve all invoices for a specific date range that have not been sent.
Solution
Show one with _out.someting - Typically running a model
Show one with $variable - If your using an action and dynamically setting the $variable.
Lookup Functionthe Region for a particular business using the City from its address.
Solution
- You should have 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.
- 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.
- In the properties window that opens for the pipe:
- Set the Name to be something short but meaningful.
- Set the Type to be
lookup
. - In the filter section we will tell the lookup pipe we want to filter
Region
byCity
using the processed city value from our Business Data.
To use the processed version of city we use _out.City. - We are passing data and not just a string value, so click to tell PhixFlow we are passing an expression.
The filter will look like this:
- The lookup is now setup, to access its attributes, we open the Business Data table and add an attribute called
Region
.- In the expression type
pipe.attribute
, in our case this will bergn.Region
. - Save your changes.
- In the expression type
on Business Data and the Region will now populate.Insert excerpt _run_analysis _run_analysis nopanel true
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 code 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. The setup will look similar to:
Solution
Merges
Matching
Simple exact matches and make reference to addition al functions for more advanced matching see Lev Distance etc.
Task PlanMerging Data
See Merging Tables for full details. This technique can also be used to deduplicate.
Matching Records
See Compare or Match Data for deduplicating and matching records.