table summarize ...

Prev Next

Function Names

table summarize, table summarize selected rows

Description

Similar to pivot tables provided by Excel, the B4P functions table summarize and table summarize selected rows create new tables containing condensed summary information. The summaries can be based on algorithmic actions of choice, for example summing up, counting, taking the average values, etc.

Features:

  • Filtering: The function table summarize selected rows allows to filter the rows of interest, ignoring the remaining ones.
  • Rows: Specify 1 or more columns in the base data and distribute the data combinations vertically across over multiple rows.
  • Columns: Specify 1 or more columns in the base data and distribute the data combinations horizontally across over multiple columns.
  • Values: Specify 1 or more columns containing the data to be summarized.
  • Actions: You can specify various algorithmic actions to create the summary, e.g. summing up, average, etc. If multiple data columns with values are specified, then different actions can be defined for each value. If no action is specified, then sum is assumed, being the most obvious action.
  • Subtotals and grand totals: Optionally includie totals and subtotals for both columns and rows, along with the level (how deep the subtotals should nest in).


table summarize - creation of pivot like summary table

Call as: function

Restrictions

Indirect parameter passing is disabled
This function provides a table context for partial table specifications with table name and row number for selected function parameters

Parameter count

3 - 8

Parameters

No.TypeDescription
1.
input
string Name of source table

This table contains the base data for the summary. This table stays unchanged.

Opt. 2.
code
expression
:string
Expression to select rows

This function parameter allows you filter the input data by selecting the rows to be included in the summary.

The table context for partial table specifications is available for referencing just the columns easily. Example: [Year]>=2022.
Attention: Comparison operators = and <> (instead of == and !=) may hijack the next function parameters for its own use as additional comparison operands, e.g. a[]=1,3,5. As long this comparison is not made in the last function parameter, then put parentheses around them, e.g. ... , (a[]=1,3,5), ....

Default value: true (all rows selected)
2 / 3.
input
string Name of new summary table

A new table with the specified name will be created for the summary information.

3 / 4.
input
table columns Data columns

Specify 1 or more columns containig the data to be be summarized. Example: { Revenue, Net Margin }.

See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function:

  • Use a string to specify one value
  • Use set to specify multiple values
  • At least one header name or column number must be specified
  • The same column with the same values below may be referenced multiple times (e.g. for calculating sums and averages of them)

Opt. 4 / 5.
input
string
set
summarizing actions

Specify the summarizing actions (click on the link to see the complete list) for the corresponding data columns specified in the previous function parameter.

Specific rules apply for this function:

  • Use a string to specify one value
  • Use set to specify multiple values
  • Ideally, the number of summarizing actions shall equal to the number of data columns specified.
  • If fewer summarizing actions are listed than number of data columns, then the last action will apply to all remaining columns.
  • If more summarizing actions are listed, then the excessive actions will be ignored.

Default value: sum
Opt. 5 / 6.
input
table columns Vertical breakdown

Optionally list columns to do a vertical break-down across the rows. For example consider { Continent, Country, City } to summarize the results down the rows, broken down by continents, followed by countries and cities. The breakdown is done in the alphabetical order.

See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function:

  • Use a string to specify one value
  • Use set to specify multiple values
  • Use {} to specify no values if further function parameters follow.
  • Any number (including 0) of header names or column number may be specified, but must be existing
  • The columns must be unique
  • The columns must not match with the data columns and/or with the horizontal breakdown columns.

Default value: {} (no columns specified for vertical breakdown)
Opt. 6 / 7.
input
table columns Horizontal breakdown

Optionally list columns to do a horizontal break-down across the columns. For example consider { Year, Quarter, Month } to summarize the results along the columns, first by year, then followed by quarter and month. The breakdown is done in the alphabetical order.

See table columns as function parameters for general ruling for this parameter.
Specific rules apply for this function: Specific rules apply for this function:

  • Use a string to specify one value
  • Use set to specify multiple values
  • Use {} to specify no values if further function parameters follow.
  • Any number (including 0) of header names or column number may be specified, but must be existing
  • The columns must be unique
  • The columns must not match with the data columns and/or with the vertical breakdown columns.

Default value: {} (no columns specified for horizontal breakdown)
Opt. 7 / 8.
input
set Attributes

A set of attributes can be defined to customize the summary. Every attribute consists of an attribute name and an attribute value. For this reason, the number of elements must be even, e.g. 0, 2, 4, etc. The attributes influnce the design and layout of the summary table. If, by mistake, the same attribute name is used repeatedly, then the last attribute assignment applies.

Attribute Names Attribute Values Description
single horizhontal header no, yes Summary table contains one top header, even if multiple horizontal breakdowns are speified. The header will be catenated using the header separator.
Default: no
single vertical header no, yes Summary table contains one left header, even if multiple vertical breakdowns are speified. The header will be catenated using the header separator.
Default: no
header separator string value Used to catenate rows / column headers into one row, Suggest a comma, new line, etc.
Default: new line
data separator string value Used to catenate rows / column headers into one row, Suggest a comma, new line, etc.
Default: ","
horizontal subtotals number 0 = No subtotals, 1 = total only, 2 = total + 1 level subtotal, 3 = total + 2 levels subtotal, ..., -1 = All subtotals, -2 = All except deepest subtotal.
Default: 0
vertical subtotals number See above.
Default: 0
data header no, yes If yes, a header row is added to show data names taken. This is useful if more than 1 data column is summarized so the names are included in the summary table.
Default: no if only 1 data column specified, otherwise yes
action header no, yes If yes, a header row is added to show the actions taken. This is useful if the same data is summarized repeatedly with different actions (e.g. sum, average, etc.).
Default: no if only 1 data column specified, otherwise yes
subtotal levels no, yes If yes, an additional row and column is added to indicate the subtotal level. 1 = Total, 2 = first subtotal, etc. Blank entries refer no totals and subtotals. ¨ Note that the row and column headers also indicate "[Sub-T]" and "[Total"] entries referring to subtotals at different levels and the grand total.
Default: no

Default value: {} (no attributes specified. See table below for defaults.)

Return value

TypeDescription
boolean Success

'true' is returned if the table has contents. 'false' indicates taht the resulting table is empty. This could be due for following reasons: * Base data contains zero rows * Function call 'table summarize selected rows' has filtered out all data, leaving zero rows of data for creating the summary.

Examples

table initialize( base data, {
       { Continent, Country, Town,         Year, Quarter, Score 1, Score 2 },
       { Europe,    UK,      London,   2025, Q1,      100,     250 },
       { Europe,    UK,      London,   2025, Q1,      120,     260 },
       { Europe,    UK,      London,   2025, Q2,      110,     270 },
       { Europe,    UK,      London,   2025, Q3,      100,     280 },
       { Europe,    UK,      London,   2026, Q1,      110,     270 },
       { Europe,    UK,      Bristol,  2026, Q1,      140,     250 },
       { Europe,    France,  Paris,    2025, Q1,      120,     280 },
       { Europe,    France,  Lyon,     2025, Q1,      150,     270 },
       { Europe,    France,  Paris,    2025, Q2,      160,     250 },
       { Europe,    France,  Lyon,     2025, Q3,      120,     280 },
       { Europe,    France,  Lyon,     2026, Q1,      130,     290 },
       { Europe,    France,  Lyon,     2026, Q1,      120,     250 },
       { Asia,      Japan,   Tokyo,    2025, Q1,      110,     220 },
       { Asia,      Japan,   Osaka,    2025, Q1,      120,     230 },
       { Asia,      Japan,   Tokyo,    2025, Q1,      130,     240 },
       { Asia,      Japan,   Osaka,    2025, Q1,      140,     250 } } );


echo("Start pout simple, without any breakdown:");

table summarize( base data, pivot table, {Score 1, Score 2}, {sum, min} );
table list( pivot table );


echo("Pivot table without subtotals:");

table summarize( base data, pivot table, {Score 1, Score 2}, {sum, min}, {Continent, Country, Town}, {Year, Quarter}  );
table list( pivot table );


echo("Pivot table with subtotals:");

table summarize( base data, pivot table, {Score 1, Score 2}, {sum, min}, {Continent, Country, Town}, {Year},
                                { horizontal subtotals, -1, vertical subtotals, -1, single horizontal header, yes }  );
table list( pivot table );


echo("Pivot table with subtotals and subtotal levels (vertical only):");

table summarize( base data, pivot table, {Score 1, Score 2}, {sum, min}, {Continent, Country, Town}, {},
                                { vertical subtotals, -1,  subtotal levels, yes}  );
table list( pivot table );


echo("London and Tokyo only:");

rv[] = table summarize selected rows( base data, ([Town]=London,Tokyo),
                                 pivot table, {Score 1, Score 2}, {sum, min}, {Country, Town}, Year  );
table list( pivot table );
echo("Return value: ", rv[] );


echo("Empty table:");

rv[] = table summarize selected rows( base data, ([Town]=Madrid), // No Madrid inside, 0 rows summarized.
                                 pivot table, {Score 1, Score 2}, {sum, min}, {Country, Town}, Year  );
table list( pivot table );
echo("Return value: ", rv[] );

Output

Start pout simple, without any breakdown:
    0 : [Data]   | Score 1 | Score 2
    1 : [Action] | sum     | min    
    2 :          | 1980    | 220    

Pivot table without subtotals:
    0 : Continent | Country | Town    | Year     | 2025    | 2025    | 2025    | 2025    | 2025    | 2025    | 2026    | 2026   
    1 :           |         |         | Quarter  | Q1      | Q1      | Q2      | Q2      | Q3      | Q3      | Q1      | Q1     
    2 :           |         |         | [Data]   | Score 1 | Score 2 | Score 1 | Score 2 | Score 1 | Score 2 | Score 1 | Score 2
    3 :           |         |         | [Action] | sum     | min     | sum     | min     | sum     | min     | sum     | min    
    4 : Asia      | Japan   | Osaka   |          | 260     | 230     |         |         |         |         |         |        
    5 : Asia      | Japan   | Tokyo   |          | 240     | 220     |         |         |         |         |         |        
    6 : Europe    | France  | Lyon    |          | 150     | 270     |         |         | 120     | 280     | 250     | 250    
    7 : Europe    | France  | Paris   |          | 120     | 280     | 160     | 250     |         |         |         |        
    8 : Europe    | UK      | Bristol |          |         |         |         |         |         |         | 140     | 250    
    9 : Europe    | UK      | London  |          | 220     | 250     | 110     | 270     | 100     | 280     | 110     | 270    

Pivot table with subtotals:
    0 : Continent | Country | Town    | Year     | 2025    | 2025    | 2026    | 2026    | [Total] | [Total]
      :           |         |         | [Data]   | Score 1 | Score 2 | Score 1 | Score 2 | Score 1 | Score 2
      :           |         |         | [Action] | sum     | min     | sum     | min     | sum     | min    
    1 : Asia      | Japan   | Osaka   |          | 260     | 230     |         |         | 260     | 230    
    2 : Asia      | Japan   | Tokyo   |          | 240     | 220     |         |         | 240     | 220    
    3 : Asia      | Japan   | [Sub-T] |          | 500     | 220     |         |         | 500     | 220    
    4 : Asia      | [Sub-T] | [Sub-T] |          | 500     | 220     |         |         | 500     | 220    
    5 : Europe    | France  | Lyon    |          | 270     | 270     | 250     | 250     | 520     | 250    
    6 : Europe    | France  | Paris   |          | 280     | 250     |         |         | 280     | 250    
    7 : Europe    | France  | [Sub-T] |          | 550     | 250     | 250     | 250     | 800     | 250    
    8 : Europe    | UK      | Bristol |          |         |         | 140     | 250     | 140     | 250    
    9 : Europe    | UK      | London  |          | 430     | 250     | 110     | 270     | 540     | 250    
   10 : Europe    | UK      | [Sub-T] |          | 430     | 250     | 250     | 250     | 680     | 250    
   11 : Europe    | [Sub-T] | [Sub-T] |          | 980     | 250     | 500     | 250     | 1480    | 250    
   12 : [Total]   | [Total] | [Total] |          | 1480    | 220     | 500     | 250     | 1980    | 220    

Pivot table with subtotals and subtotal levels (vertical only):
    0 : Continent | Country | Town    | Level | [Data]   | Score 1 | Score 2
    1 :           |         |         |       | [Action] | sum     | min    
    2 :           |         |         | Level |          |         |        
    3 : Asia      | Japan   | Osaka   |       |          | 260     | 230    
    4 : Asia      | Japan   | Tokyo   |       |          | 240     | 220    
    5 : Asia      | Japan   | [Sub-T] | 3     |          | 500     | 220    
    6 : Asia      | [Sub-T] | [Sub-T] | 2     |          | 500     | 220    
    7 : Europe    | France  | Lyon    |       |          | 520     | 250    
    8 : Europe    | France  | Paris   |       |          | 280     | 250    
    9 : Europe    | France  | [Sub-T] | 3     |          | 800     | 250    
   10 : Europe    | UK      | Bristol |       |          | 140     | 250    
   11 : Europe    | UK      | London  |       |          | 540     | 250    
   12 : Europe    | UK      | [Sub-T] | 3     |          | 680     | 250    
   13 : Europe    | [Sub-T] | [Sub-T] | 2     |          | 1480    | 250    
   14 : [Total]   | [Total] | [Total] | 1     |          | 1980    | 220    

London and Tokyo only:
    0 : Country | Town   | Year     | 2025    | 2025    | 2026    | 2026   
    1 :         |        | [Data]   | Score 1 | Score 2 | Score 1 | Score 2
    2 :         |        | [Action] | sum     | min     | sum     | min    
    3 : Japan   | Tokyo  |          | 240     | 220     |         |        
    4 : UK      | London |          | 430     | 250     | 110     | 270    

Return value: true
Empty table:
    0 : Country | Town | Year    
    1 :         |      | [Data]  
    2 :         |      | [Action]

Return value: false
Try it yourself: Open LIB_Function_table_summarize.b4p in B4P_Examples.zip. Decompress before use.

See also

table consolidate
table consolidate selected rows
table spread