Collect rows according to shared criteria and perform operations. A step-by-step. Now available to be added with 'sort' directive.
'Group' can now be used with 'sort' directive
Simply toggle on the 'sort' directive when adding 'group' & start sorting your columns based on ascending or descending order.
This works similarly as 'Sort' standalone directive.
Grouping rows in a worksheet based on common criteria is a powerful data processing technique. Users may group rows of data per specified criteria. Once rows are grouped, other operations can be applied to the group as an added convenience.
For example, consider a worksheet with columns "Account ID" and "Date". Use this directive to group the rows in this worksheet.
pebblestream:from(Accounts) pebblestream:group("Account ID", "Date")
All rows with matching "Account ID" and "Date" values are put next to each other. This often results in easier data processing.
You may also add sub-directives which allows you to perform convenient operations such as sum and coalesce.
Adding 'group' directive
Step 1: Click the "Add Directive" button on your dashboard
Step 2: Choose "Group"
Step 3: Select the worksheet you'd want to group
This will be the worksheet you'll be getting your data from.
Step 4: Select the column(s) you'd like to group
With 'group' directive, columns selected will also be put side-by-side.
When selecting column(s) to be grouped, rows that have the same values will be put next to each other. This often results in easier data processing.
Grouping only the column 'First' will come back with the following result where the rows of the same values in 'First' will be put next to each other.
If you were to group 'First' and 'Last', Zen will come back with the following results where the rows of the same values in both 'First' and 'Last' will be put next to each other. (Values have to be the exact same for 'First' and 'Last'.)
Step 4.1: Adding sub-directive
What are sub-directives?
Sub-directives are additional operations that can be added when adding 'group' as an added convenience.
What can each of these directives do?
|Coalesce||To combine rows of grouped column(s) that have the same values. This is especially useful to avoid data duplicates.||Only available when adding another sub-directive|
|Sum||To get the sum of the values in a column for rows grouped by criteria||Works only on numbers|
|Mult||Multiply rows of a column that share the same values|
|Equal||Group rows that have equal values in the provided column|
|Ratio||Given a group of numbers, determine the relative weight of each number|
|Merge-Last-Not-Blank||Collapse values keeping only the last, non empty cell|
For more detailed documentation of each of these sub-directives:
Step 5: Once you're happy with your selection, click 'Next' and proceed to the next screen
Step 6: Name your output worksheet
This will be where your output data from connecting the worksheet by adding 'group' will be. Coalesce can also be added here!
Step 7: Once you've inputted your output worksheet name, you're ready to add the directive!
Your spreadsheet will automatically take you to the output worksheet. See the results of grouping the data in your worksheet!
For more technical and in-depth information on our 'filter' directive, do visit our Pebble Stream Enterprise Documentation
Updated about 1 month ago