B4P Reference

Function Names

table extend, table extend selected rows

Description

This function extends the table by breaking up the contents in 1 or multiple columns into smaller items and distribute these items over additional rows being inserted below. As an example, one field contains multiple values (numbers, text elements, etc) separated with commas and need to be put into separate rows: 2,3,5,7,11 would create 4 additional rows and put the 5 indiviudal numbers into each of the rows. When rows are inserted, all columns except those with data to be broken up will be duplicated.
Note: This function may only insert further rows. No rows are deleted in case the contents to break up are blank.

table extend covers all rows to extend where needed.
table extend selected rows covers the selected rows only.

Call as: procedure or 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

2 - 5

Parameters

No.	Type	Description
1. input	string	Name of existing table
Opt. 2. code	expression :string	Expression to select rows Applicable for table extend selected rows only. Specify the conditions or rules to select the rows. See expressions to select rows. 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), ....
2. / 3. input	table columns	Columns with contents to be broken apart The contents in the specified columns will be broken apart and distributed across multiple rows after being inserted. All types are accepted: Numeric, text, dates, sets, etc. Following rules apply for multiple columns specified: The column containing the highest number of items after breaking up determines the number of rows to be added. For example, if 3 columns are specified, and the contents in a particular row can be broken into 3, 5 and 7 items, then 6 new rows are inserted, so the total number of rows is 7. For the columns where the number of items are smaller, then the remaining rows at the bottom stay blank.., blanks are applied in the remaining rows. See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: At least one header name or column number must be specified. Default value:
3. / 4. input	string set	Separator symbols In order to break up the contents in the specified columns, a separator symbol needs to be specified for each column. The separator symbol must contain at least one character. Typical separator symbols could be comma, semicolon, new line, slashes, etc. Note: Dangling blanks, e.g. Mike , John , Jane are not removed if the separator symbol contains just the comma and not a comma with space before and/or after. If the previous parameter contains multiple columns, then different spearators can be applied. If the number of elements is less than the number of columns specified, then the last element will apply for the remaining columns. If more elements are provided, than the extraneous ones will be ignored. Alternatively, following softquoted string values (specify them single quotation marks) can be used for special cases: #qty If a number is found in the column specified by the previous parameter, then it specifies the number of rows and counts them up beginning with 1. #zero Same as above, but counting begins with zero and ends with number-1. #chars The contents specified by the previous parameter will be broken up into characters. The character count in the string determins the number of rows to be inserted. The string will be broken down into individual characters across the inserted rows. #set A set is expected in the column specified by the previous parameter. Sets are created in a similar way as by the set() function. Braces are not required, but the elements will be separated into individual rows. Nested elements will not be broken apart. Example: { 'Mike' , John , " Abel ", {x,y} } assumes 4 rows containing Mike, John, Abel and {x,y}, all except " Abel " without dangling spaces. Note: The syntax of the sets must be valid, i.e. no imbalanced braces, e.g. {{..}, or text improperly put into quotation marks. Otherwise, the set will not be broken up. Default value: , (comma)
Opt. 4. / 5. input	table columns	Existing columns to copy See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: Any number of header names and column numbers may be specified The columns listed must be unique, i.e. not repeated. Columns overlapping with those specified to be broken up will be ignored Specify '' (asterisk as softquoted string) alone to select all columns Note: If the columns specified here are also selected as columns where data is to be broken apart, then the copying takes place before data is broken down. E.g. if 5 rows are inserted, but a the column breaks into 3 rows, then the remaining 2 rows contain the copies of the original contents. Default value:* (None)
Opt. 5. / 6. input	table column	Index column See table columns as function parameters for general ruling for this parameter. Specific rules apply for this function: Max 1 header names or column number may be specified If the column is not existing, then it will created and added after the right-most existing column What will be written into the index column if n rows are inserted below or above:

Return value

Type	Description
numeral	Number of rows identified Only the number of identified rows (and not the number of inserted rows) is returned. -1 is returned if table is empty.

Examples

table initialize( base data, {

                { Name,      Features 1,   Features 2,     Text,      Numbers, Gender },

                { Nick,      "Hi/He/Hu",   "Me, You, He",  ABCDE,     2,       M },

                { Jane,      "She/Her",    "1, 01",        A,         3,       F },

                { Nobody,    "",           "",             '',        0,       ''},

                { Sophie,    " He / She",  "{ He , She }", SO,        1,       F } } );



table copy table( base data, copy );



echo("Same, copy down Name and Features 1:");

a[] = table extend( base data,

                { Features 1, Features 2, Text,     Numbers },

                { '/',        '#set',     '#chars', '#qty'  }, {Name, Features 1} , Index );



table list( base data );

echo("Number of rows extended: ", a[], new line);



echo("Same, but copy all columns, count from zero, and Nick and Sophie only:");



a[] = table extend selected rows( copy, ([Name]=Nick,Sophie),

                    { Features 1, Features 2, Text,     Numbers },

                    { '/',        '#set',     '#chars', '#zero' }, '*' , Index );



table list( copy );

echo("Number of rows extended: ", a[]);

Output

Same, copy down Name and Features 1:
    0 : Name   | Features 1 | Features 2 | Text | Numbers | Gender | Index
    1 : Nick   | Hi         | Me         | A    | 1       | M      | 0    
    2 : Nick   | He         | You        | B    | 2       |        | 1    
    3 : Nick   | Hu         | He         | C    |         |        | 2    
    4 : Nick   | Hi/He/Hu   |            | D    |         |        | 3    
    5 : Nick   | Hi/He/Hu   |            | E    |         |        | 4    
    6 : Jane   | She        | 1          | A    | 1       | F      | 0    
    7 : Jane   | Her        | 01         |      | 2       |        | 1    
    8 : Jane   | She/Her    |            |      | 3       |        | 2    
    9 : Nobody |            |            |      | 0       |        | 0    
   10 : Sophie |  He        | He         | S    | 1       | F      | 0    
   11 : Sophie |  She       | She        | O    |         |        | 1    

Number of rows extended: 4

Same, but copy all columns, count from zero, and Nick and Sophie only:
    0 : Name   | Features 1 | Features 2  | Text | Numbers | Gender | Index
    1 : Nick   | Hi         | Me          | A    | 0       | M      | 0    
    2 : Nick   | He         | You         | B    | 1       | M      | 1    
    3 : Nick   | Hu         | He          | C    | 2       | M      | 2    
    4 : Nick   | Hi/He/Hu   | Me, You, He | D    | 2       | M      | 3    
    5 : Nick   | Hi/He/Hu   | Me, You, He | E    | 2       | M      | 4    
    6 : Jane   | She/Her    | 1, 01       | A    | 3       | F      |      
    7 : Nobody |            |             |      | 0       |        |      
    8 : Sophie |  He        | He          | S    | 0       | F      | 0    
    9 : Sophie |  She       | She         | O    | 1       | F      | 1    

Number of rows extended: 2

Try it yourself: Open LIB_Function_table_extend.b4p in B4P_Examples.zip. Decompress before use.

table extend, ... selected rows