transformations:subfield
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:subfield [2018/07/03 20:49] – dmitry | transformations:subfield [2021/04/21 15:35] – [Remarks] craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Split delimited text into rows ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Advanced \\ | ||
- | Split column values delimited with a separator (e.g. comma) into multiple rows -- one row per value. Values in other columns are duplicates for the new rows. | + | \\ |
+ | =====Description===== | ||
+ | This action splits | ||
- | For instance, a value with 3 words delimited with semicolons will be split into three rows: | + | Values in other columns are duplicated in the new rows.\\ |
- | ^Row no ^Numbers ^ | + | \\ |
- | | 1|one; | + | =====Use cases===== |
+ | This action can be useful in cases where individual values are concatenated, | ||
- | Delimiter: Semicolon | + | \\ |
+ | =====Action settings===== | ||
+ | ^Setting^Description^ | ||
+ | |Column|Select the column containing the delimited text to be split.| | ||
+ | |Separator|Select the delimiting character to split the text by. Options: // | ||
+ | |Ignore quoting |When selected, delimiting characters contained within paired quotation marks will be used for splitting text. | | ||
+ | \\ | ||
+ | =====Remarks===== | ||
+ | Unlike the [[transformations: | ||
+ | |||
+ | Leading and trailing whitespace are automatically removed from values when the new columns are created.\\ | ||
+ | |||
+ | \\ | ||
+ | =====Examples===== | ||
+ | |||
+ | **Example 1:** A value with 3 words, delimited with semicolons, will be split into three rows:\\ | ||
+ | |||
+ | **Source table:** | ||
+ | ^Row no ^Numbers ^ | ||
+ | | 1|one; | ||
+ | \\ | ||
+ | **Action parameters: | ||
+ | > Column: " | ||
+ | > Delimiter: Semicolon | ||
+ | \\ | ||
+ | **Result:** | ||
^ Row no ^ Numbers ^ | ^ Row no ^ Numbers ^ | ||
| 1|one | | | 1|one | | ||
| 1|two | | | 1|two | | ||
| 1|three | | | 1|three | | ||
- | | ||
- | Another example: | ||
- | A column with multi-line text values. | ||
+ | \\ | ||
+ | **Example 2:** A column with multi-line text values.\\ | ||
+ | |||
+ | **Source table:** | ||
^ Location No ^ Location ^ | ^ Location No ^ Location ^ | ||
| 1| Mississauga \\ Ontario \\ Canada \\ North America | | | 1| Mississauga \\ Ontario \\ Canada \\ North America | | ||
| 2| Cambridge \\ Massachusetts \\ United States \\ North America | | | 2| Cambridge \\ Massachusetts \\ United States \\ North America | | ||
- | + | \\ | |
- | Delimiter: Line break | + | **Action parameters: |
+ | > Column: " | ||
+ | > Delimiter: Line break | ||
+ | \\ | ||
+ | **Result:** | ||
^ Location No ^ Location ^ | ^ Location No ^ Location ^ | ||
| 1| Mississauga | | 1| Mississauga | ||
Line 34: | Line 68: | ||
| 2| North America | | 2| North America | ||
- | ===See also=== | + | \\ \\ |
+ | **Example 3:** Split out Order IDs into rows to create unique Customer-Order ID records.\\ | ||
+ | |||
+ | **Source table:** | ||
+ | ^Customer ID^Order IDs^ | ||
+ | |CUST001 | ||
+ | |CUST002 | ||
+ | |CUST003 | ||
+ | \\ | ||
+ | **Action parameters: | ||
+ | > Column: "Order IDs" | ||
+ | > Separator: Comma | ||
+ | \\ | ||
+ | **Result: | ||
+ | ^Customer ID^Order IDs^ | ||
+ | |CUST001 | ||
+ | |CUST001 | ||
+ | |CUST001 | ||
+ | |CUST002 | ||
+ | |CUST002 | ||
+ | |CUST002 | ||
+ | |CUST003 | ||
+ | |CUST003 | ||
+ | |CUST003 | ||
+ | |CUST003 | ||
+ | \\ | ||
+ | =====See also===== | ||
+ | * [[transformations: | ||
* [[transformations: | * [[transformations: | ||
- | * [[transformations: | ||
transformations/subfield.txt · Last modified: 2023/10/11 20:54 by craigt