What is Folding
The folding option is an advanced mechanism for transposing data displayed in views within Syntelic. Generally, the grouping (or “group-by”) function is a means to transform a set of data rows into another set of rows by summarizing the original rows based on common values of certain specified keys. Thus, the group-by process generally ends up with a lesser number of rows than the starting point.
Folding, however, does the reverse — it is an option that executes just before the first group-by process (group-by-1) which may actually increase the number of rows displayed in a view.
Folding is used when it is desired to split one row into two or more rows by transposing certain columns into rows. The view’s folding count indicates how many rows should be generated from each row that begins with the group-by-1 step. So, for example, folding count = 2 will create 2 rows for each input row and we will potentially end up with double the number of rows with which we started. A folding count of 1 would create one potential output row for each input, and represents the normal default process with no folding.
Let’s Look at an Example
The folding process starts by calculating the number of keys per fold. Let’s say we defined 6 group-by-1 keys, and we have a folding count of 3. That means that we have 2 keys per fold. Because of the way folding works, it’s important that the folding count be evenly divisible into the total number of group-by-1 keys.
At the end of the folding process, the keys of each fold will be transposed, or folded, onto the columns of the first fold, and the values of all group-by keys after the first fold will have been cleared to blanks or zeroes.
Going back to our example, let’s call our group-by-1 keys K1, K2, K3, K4, K5, and K6, listed in display column sequence. And let’s assume there are four additional columns, which are not group-by keys, named D1, D2, D3, and D4. Let’s assume we had an input row that looked like the following table:
During the folding process, the first output row will be created by copying the input row just as it is. The second output row will start in the same manner, by making a copy of the input row, but then the value of K3 will be moved to the K1 column, and K4 will be moved to the K2 column. And finally, for the third row, after making a copy of the input, K5-K6 will be moved to K1-K2.
In all three output rows, keys K3-K6 will be cleared as the final step in folding. In addition, note that all of the data (non-key) columns, D1-D4, keep their original values on all 3 output rows. The results will look something like the following:
Points to Remember
- Folding runs right before the group-by-1 step.
- The folding count, specified in the view, represents the number of output rows that will be created for each input row.
- The folding count must be evenly divisible into the number of keys in the group-by-1 step.
- For folding to be effective, the keys that are folded on top of one another must have the same data type (such as text string, or date, or number). In our example above, K1, K3 and K5 must all have the same data type because their values will all end up in the same column (K1) after folding. The same can be said for K2, K4, and K6.
- After folding, the group-by-1 process continues as usual. It is possible that the group-by process will summarize the folded data rows, resulting in fewer rows, if some rows have the same group-by key values. Or, if each row’s group-by keys (after folding) represents a distinct unique value, the group-by process will end up with the same number of rows as produced by folding .