transformations:subfield
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:subfield [2016/06/19 19:38] – created dmitry | transformations:subfield [2021/04/20 13:43] – craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Split column into rows ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Advanced \\ | ||
- | Split column values delimited with a separator (e.g. comma) into multiple rows -- one row per value. | + | \\ |
+ | =====Description===== | ||
+ | This action splits | ||
- | For instance, a column | + | \\ |
+ | =====Use cases===== | ||
+ | This action can be useful in cases where individual values are concatenated, with a delimiter, in a single | ||
- | | + | \\ |
- | + | =====Action settings===== | |
- | will be split into three rows | + | ^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: | ||
+ | |||
+ | \\ | ||
+ | =====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 ^ | ||
+ | | 1|one | | ||
+ | | 1|two | | ||
+ | | 1|three | | ||
+ | |||
+ | \\ \\ | ||
+ | **Example 2:** A column with multi-line text values.\\ | ||
+ | |||
+ | **Source table:** | ||
+ | ^ Location No ^ Location ^ | ||
+ | | 1| Mississauga \\ Ontario \\ Canada \\ North America | | ||
+ | | 2| Cambridge \\ Massachusetts \\ United States \\ North America | | ||
+ | \\ | ||
+ | **Action parameters: | ||
+ | > Column: " | ||
+ | > Delimiter: Line break | ||
+ | \\ | ||
+ | **Result: | ||
+ | ^ Location No ^ Location ^ | ||
+ | | 1| Mississauga | ||
+ | | 1| Ontario | ||
+ | | 1| Canada | ||
+ | | 1| North America | ||
+ | | 2| Cambridge | ||
+ | | 2| Massachusetts | ||
+ | | 2| United States | ||
+ | | 2| North America | ||
+ | |||
+ | \\ \\ | ||
+ | **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: | ||
- | one | ||
- | two | ||
- | three | ||
- | | ||
- | Values in other columns are duplicates for the new rows. |
transformations/subfield.txt · Last modified: 2023/10/11 20:54 by craigt