N_PER_TABLE

Returns the result set of all records that meet the criteria defined as a boolean value, which can be used to reference that single record among the children that reference a parent table.

N_PER_TABLE ( <table_name or expression> , <table_name> , <nvalue> [,<orderby_field_name>][, <reverse>] )

  • table_name or expression: The table or subset of records on the table, defined as an expression. Expressions work best.

  • table_name: Parent table to link the reference to the N_PER calculation.

  • nvalue: N_PER value, 1 would provide one per table, 2 would provide 2 per table, etc.

  • orderby_field_name: The field to use to order the child records when isolating the 1 per table. Once ordered the record with the largest value is retained and flagged with the value 1.

  • reverse: Optional switch to reverse the values referenced in the order by field.

Example: create a field on the INDIVIDUAL table to flag the highest spender within their HOUSEHOLD enabling the user to use this as deduplication criteria for the highest value target at the household.

N_PER_TABLE ([Individual.Order Count] GE 1, Household , 1 , [Individual.Lifetime Spend] )