Pull-up aggregation
PU_AVG
Description: Computes the average of values from a related table and pulls the result to the current table level.
Syntax: PU_AVG ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode" AS "Company", PU_AVG("companyDetail", "caseTable"."value") AS "Avg Value" )
PU_COUNT
Description: Counts related records in another table and returns the result at the current table level.
Syntax: PU_COUNT ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_COUNT("companyDetail", "caseTable"."caseId") AS "#Cases" )
PU_COUNT_DISTINCT
Description: Counts unique values from a related table and returns the result at the parent level.
Syntax: PU_COUNT_DISTINCT ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_COUNT_DISTINCT("companyDetail", "caseTable"."customerId") AS "#Customers" )
PU_FIRST
Description: Returns the first value from a related table based on event ordering.
Syntax: PU_FIRST ( target_table, source_table.column [ , filter_expression ] [ , ORDER BY source_table.column [ASC|DESC] ] )
Example:
TABLE( "companyDetail"."companyCode", PU_FIRST("companyDetail", "caseTable"."caseId", ORDER BY "caseTable"."value") AS "Smallest CaseId" )
PU_LAST
Description: Returns the last value from a related table based on event ordering.
Syntax: PU_LAST ( target_table, source_table.column [ , filter_expression ] [ , ORDER BY source_table.column [ASC|DESC] ] )
Example:
TABLE( "companyDetail"."companyCode", PU_LAST("companyDetail", "caseTable"."caseId", ORDER BY "caseTable"."value") AS "Largest CaseId" )
PU_MAX
Description: Returns the maximum value from a related table at the current table level.
Syntax: PU_MAX ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_MAX("companyDetail", "caseTable"."value") AS "Max Value" )
PU_MEDIAN
Description: Computes the median value from a related table and returns it to the parent context.
Syntax: PU_MEDIAN ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_MEDIAN("companyDetail", "caseTable"."value") AS "Median Value" )
PU_MIN
Description: Returns the minimum value from a related table at the current table level.
Syntax: PU_MIN ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_MIN("companyDetail", "caseTable"."value") AS "Min Value" )
PU_STDEV
Description: Calculates the standard deviation of values in a related table and returns it at the parent level.
Syntax: PU_STDEV ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_STDEV("companyDetail", "caseTable"."value") AS "StdDev Value" )
PU_SUM
Description: Sums values from a related table and pulls the result into the current context.
Syntax: PU_SUM ( target_table, source_table.column [ , filter_expression ] )
Example:
TABLE( "companyDetail"."companyCode", PU_SUM("companyDetail", "caseTable"."value") AS "Sum Value" )