Overview

...

Overview

A pipe is a connector that links two elements in a PhixFlow model and sends data from the input to the output. Pipes allows you to control which attributes and which records from the input are delivered by to the output, although in most cases - with minimal configuration - you will get all columns and the records from the current run.

...

Field	Description
Include History Records	Insert excerpt _check_box_untick _check_box_untick nopanel true  to filter out superseded records. Insert excerpt _check_box_ticked _check_box_ticked nopanel true  to include superseded records.
Condition	Select one of the options Where ALL... Where ANY... To add more conditions, hover your mouse pointer over this field to display the  Insert excerpt _add _add nopanel true  button.
Insert excerpt _add _add nopanel true	Hover your mouse pointer over the Condition field to display this button. Add another condition to your filter.
Clause	Select an option from the list. PhixFlow adds more fields where you can: select how the filter matches (for example, equals, contains, is null) enter a string that the filter uses to match the data. The string can be an expression or a literal string.
Insert excerpt _delete _delete nopanel true	Hover your mouse pointer over a filter clause to display this button. Delete the selected clause or condition from the filter.
Insert excerpt _filter_literal _filter_literal nopanel true	Indicates the value entered is a literal value. Click this icon to treat the value as an expression.
Insert excerpt _filter_expression _filter_expression nopanel true	Indicates the value entered is an expression. Click this icon to treat the value to a literal string. Note: ["123", "234", "345"] looks like a literal value but it can be evaluated as an expression.
	Open the expression in a larger editor.
Cache Extraction Filter	A cache extraction filter allows you to further filter the data retrieved by a pipe. These are not commonly used, but are sometimes helpful when either: Optimising performance on a lookup pipe when for a set of records, the record you require from the lookup depends on non-key data, e.g. the date When getting data from a pull pipe when the filter requires that you compare one value in each record with another; this is not possible within a standard filter. For case 1, when using a lookup pipe, data retrieved is stored in a cache. See cache size for details. The cache extraction filter allows you, as you are processing a set of output records, to use different cached entries from the lookup for each of the records are you are processing. This is very fast compared to looking up from the source (i.e. going back to an external DB table or even another PhixFlow stream) for each output record. E.g. you want to look up the credit rating for a customer for a set of transactions - in the output, each transaction is represented by a single output record.  You create an indexed lookup pipe using CustNo as the key for the index. This means that for each new CustNo you encounter in the data, all the credit rating entries for that CustNo would be retrieved by the pipe and placed into the cache. The credit rating for each customer is fully historied, so you get a number of entries for each CustNo. To get the relevant lookup entry for each output report (each transaction), you need to compare the transaction date of the output record to the dates of credit rating entries in the cache. So to extract the relevant record, you include a cache extraction filter in the form: Code Block StartDate >= _out.TransDate && (EndDate <= _out.TransDate || EndDate == _NULL) Cache extraction filters are entered free hand. The attribute names referenced must exist in a stream. This means that the each attribute must be one of: an attribute in a source stream, if you are reading from a stream if you are reading from an external database table, one of the fields returned by the database collector AND an attribute in the output stream. This means to use an attribute with the source as a database collector, there must be an attribute of matching name in the output stream an attribute in the destination stream, in which case you will refer to it using the format _out.AttributeName

Filter Examples

Filter on Current User

Sometimes when running analysis you want to select, from the source, only records belonging to the currently logged in user. To set a filter where, say, an attribute in the source Owner equals the current logged in user, add a condition to the filter like this:

Owner Equals _user.name fx

Enter a list of values for an "Is In" or "Is Not In" filter

If you want to based on a list of values, use the Is in or Is not in comparators, then type the list of values into the comparison field as a comma separated list like this:

Country Is in England, France, Germany ABC

In this case you must NOT click the ABC icon to convert the value to an fx, because this will indicate that the value is a formula; it must be left as a literal value. If you do click the ABC icon, then the value must be entered like this:

Country Is in ["England","France","Germany"] fx

Sort/Group

Use this section to group and sort data as it comes through the pipe.

This section has a toolbar with standard buttons. The grid contains a list of attributes from the input stream.

To add a stream attributes to the list:

...

For input stream attributes, PhixFlow displays the attribute name. (Read-only)

For a new attribute, enter a name.

...

Select the sort order

• (A-Z) to sort data records in ascending order, e.g. A to Z, 1 to 9, earliest to latest date.
• (Z-A) to sort data records in descending alpha-numeric order, e.g. Z to A, 9 to 1, latest to earliest date.

...

Aggregate Attributes

1. if you are reading from an external database table, one of the fields returned by the database collector AND an attribute in the output stream - i.e. to use an attribute with the source as a database collector, there must be an attribute of matching name in the output stream
2. an attribute in the destination stream, in which case you will refer to it using the format _out.AttributeName

...

A Pipe can be grouped and sorted by attributes of the input stream. These are set up in the Group/Order section of the Pipe form. In fact, this section is called Sort/Group for pull and push pipes, and Order/Index for lookup pipes.

The following fields are configured at the level of the pipe:

...

This setting is only available for lookup pipes.

Look-up pipes can be configured for fast "indexed" access to cached data collected from external tables, files or from other streams. Indexed access is controlled through configuring a pipe with an index and setting index expressions on grouping attributes. If the Type field on the Pipe is set to 'Look-up' then the field "Index Type" becomes available. This can have the value "None" meaning that there are no index keys or "Exact Match", "Best Match" or "Near Match" as described below:

• Exact Match: The pipe will retrieve data from its cache based on an exact match look-up with the values provided after evaluating the index expressions on the "Group By" attributes.
• Best Match: The pipe will retrieve data from its cache based on a "Best Match" look-up after evaluating the index expressions on the "Group By" attributes. Note that the last Group By Attribute with a key expression is used for the best match lookup. The index keys on any Group By attributes with a lower sequence number are used as an initial "Exact Match" to find the set of data on which to do the "Best Match". The "Best Match" is defined as the longest key value which matches the evaluated index expression.
• Near Match: The pipe will retrieve data from its cache based on a "Near Match" look-up after evaluating the index expressions on the "Group By" attributes. Note that the last Group By Attribute with a key expression is used for the near match lookup. The index keys on any Group By attributes with a lower sequence number are used as an initial "Exact Match" to find the set of data on which to do the "Best Match". When "Near Match" is selected, an additional field appears where you can enter an expression which should evaluate to a number representing the allowed number of edits (e.g. deletions, insertions, substitutions and transpositions) which can be made when comparing the result of the index expression to the index key in order to achieve a match.
  For example if the index key is "Smyhte" and the result of the index expression is "Smith" this would still be a match providing that the allowed number of edits is 3 or more (i.e. substitute the 'i' for a 'y', transpose the 't' and the 'h' and then insert an 'e' at the end).

Form: Grouping Attribute Details

The following fields are configured for each grouping attribute:

...

This field is only available for lookup pipes where you have selected indexing.

If the pipe is configured as a Look-up with an index match type set, this field becomes available. Look-up pipes can be configured for fast "indexed" access to cached data. This data is collected from external tables, files or from other streams. Indexed access is controlled through configuring a pipe with an index and setting index expressions on "Group By" attributes here.

...

StartDate >= _out.TransDate && (EndDate <= _out.TransDate || EndDate == _NULL)

Filter Examples

Filter on Current User

Sometimes when running analysis you want to select, from the source, only records belonging to the currently logged in user. To set a filter where, say, an attribute in the source Owner equals the current logged in user, add a condition to the filter like this:

Owner Equals _user.name fx

Enter a list of values for an "Is In" or "Is Not In" filter

If you want to based on a list of values, use the Is in or Is not in comparators, then type the list of values into the comparison field as a comma separated list like this:

Country Is in England, France, Germany ABC

In this case you must NOT click the ABC icon to convert the value to an fx, because this will indicate that the value is a formula; it must be left as a literal value. If you do click the ABC icon, then the value must be entered like this:

Country Is in ["England","France","Germany"] fx

Sort/Group

Use this section to group and sort data as it comes through the pipe. for lookup pipes, this section is called Order/Index. This section has:

• a toolbar with standard buttons
• a grid that lists the attributes that you want to sort or use to group
• below the grid are the following options:

Using the Sort/Group Grid

To add a stream attributes to the list:

Aggregate Attributes

Aggregate Attributes define the aggregated properties that are available when data is read from an aggregating Pipe. Note that Aggregate Attributes are not available on Pipes from Database Collectors (any aggregation can be performed in the query SQL), nor are they available on Pipes from File Collectors.

Possible aggregate values are counts, summations, averages and maximum or minimum values of Stream Items grouped in the Group/Order tab of the Pipe.

Use this section to combine data from different attributes it comes through the pipe. This section has:

• a toolbar with standard buttons
• a grid that lists the attributes that you want to aggregate.

.

Aggregate Attributes define the aggregated properties that are available when data is read from an aggregating Pipe. Note that Aggregate Attributes are not available on Pipes from Database Collectors (any aggregation can be performed in the query SQL), nor are they available on Pipes from File Collectors.

Possible aggregate values are counts, summations, averages and maximum or minimum values of Stream Items grouped in the Group/Order tab of the Pipe.

...