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/01/24 22:01] – updated example 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. 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: | + | \\ |
+ | =====Use cases===== | ||
+ | This action can be useful in cases where individual values are concatenated, with a delimiter, in a single column that should | ||
- | ^Row no ^Numbers | + | \\ |
- | | 1|one; | + | =====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 | ||
- | Delimiter: Semicolon | + | \\ |
+ | =====Remarks===== | ||
+ | Unlike the [[transformations:splitdelimitedtext|Split delimited text into columns]] action, in which you have to define the new columns to receive the split values, this action will create as many new rows as necessary to output all split values in the selected column. | ||
+ | \\ | ||
+ | =====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 64: | ||
| 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