Simple Consolidation Functions
Simple consolidation functions select a value from all the port values in a record group. When you use a simple consolidation function, you provide the function with a port and the group by port.
CONSOL_AVG
Analyzes a port in the record group and returns the average of all values.
Syntax
CONSOL_AVG(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The average of all values in a port.
For String and Date/time datatypes, the function returns the most frequently occuring value.
Example
The following expression uses the CONSOL_AVG function to find the average value of the SalesTotal input port:
SalesTotal1:= CONSOL_AVG(SalesTotal, GroupKey)
In this expression, the CONSOL_AVG function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the SalesTotal port and returns the average value. The expression writes the average value to the SalesTotal1 output port.
CONSOL_LONGEST
Analyzes a port in the record group and returns the value with the highest character count.
Syntax
CONSOL_LONGEST(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The port value with the highest character count.
If the highest character count is shared by two or more values, the strategy returns the first qualifying value.
Example
The following expression uses the CONSOL_LONGEST function to analyze the FirstName input port and find the value with the highest character count:
FirstName1:= CONSOL_LONGEST(FirstName, GroupKey)
In this expression, the CONSOL_LONGEST function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the FirstName port and returns the longest value. The expression writes this value to the FirstName1 output port.
CONSOL_MAX
Analyzes a port in the record group and returns the highest value.
Syntax
CONSOL_MAX(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The highest port value.
For the String datatype, the function returns the longest string. For the Date/time datatype, the function returns the most recent date.
Example
The following expression uses the CONSOL_MAX function to analyze the SalesTotal input port and find the highest value:
SalesTotal1:= CONSOL_MAX(SalesTotal, GroupKey)
In this expression, the CONSOL_MAX function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the SalesTotal port and returns the highest value. The expression writes this value to the SalesTotal1 output port.
CONSOL_MIN
Analyzes a port in the record group and returns the lowest value.
Syntax
CONSOL_MIN(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The lowest port value.
For the String datatype, the function returns the shortest string. For the Date/time datatype, the function returns the earliest date.
Example
The following expression uses the CONSOL_MIN function to analyze the SalesTotal input port and find the lowest value:
SalesTotal1:= CONSOL_MIN(SalesTotal, GroupKey)
In this expression, the CONSOL_MIN function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the SalesTotal port and returns the lowest value. The expression writes this value to the SalesTotal1 output port.
CONSOL_MOSTFREQ
Analyzes a port in the record group and returns the most frequently occurring value, including blank or null values.
Syntax
CONSOL_MOSTFREQ(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The most frequently occurring value, including blank or null values.
If the highest number of occurences is shared by two or more values, the strategy returns the first qualifying value.
Example
The following expression uses the CONSOL_MOSTFREQ function to analyze the Company input port and find the most frequently occurring value:
Company1:= CONSOL_MOSTFREQ(Company, GroupKey)
In this expression, the CONSOL_MOSTFREQ function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the Company port and returns the most frequently occurring value. The expression writes this value to the Company1 output port.
CONSOL_MOSTFREQ_NB
Analyzes a port in the record group and returns the most frequently occurring value, excluding blank or null values.
Syntax
CONSOL_MOSTFREQ_NB(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The most frequently occurring value, excluding blank or null values.
If the highest number of occurences is shared by two or more values, the strategy returns the first qualifying value.
Example
The following expression uses the CONSOL_MOSTFREQ_NB function to analyze the Company input port and find the most frequently occurring value:
Company1:= CONSOL_MOSTFREQ_NB(Company, GroupKey)
In this expression, the CONSOL_MOSTFREQ_NB function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the Company port and returns the most frequently occurring value. The expression writes this value to the Company1 output port.
CONSOL_SHORTEST
Analyzes a port in the record group and returns the value with the lowest character count.
Syntax
CONSOL_SHORTEST(string, group by)
The following table describes the arguments for this command:
Argument | Required/Optional | Description |
|---|
string | Required | Input port name. |
group by | Required | Name of the input port that contains the group identifier. |
Return Value
The port value with the lowest character count.
If the lowest character count is shared by two or more values, the strategy returns the first qualifying value.
Example
The following expression uses the CONSOL_SHORTEST function to analyze the FirstName input port and find the value with the lowest character count:
FirstName1:= CONSOL_SHORTEST(FirstName, GroupKey)
In this expression, the CONSOL_SHORTEST function uses the GroupKey port to identify a record group. Within that record group, the function analyzes the FirstName port and returns the shortest value. The expression writes this value to the FirstName1 output port.