Edit String in Beastmode or ETL
Hello friends.
I have a field where there are outputs like this:
"Electronics | Computers & Accessories | Computer Accessories | Printer Ink & Toner"
I want to only display the text after the last | separator. So the field would display only:
"Printer Ink & Toner"
Should I do this in ETL or Beastmode? And how do I do it?
Many thanks.
Comments
-
Although I haven't used it myself I have been investigating using the Split Column function in Magic ETL
If your data your referncing is contained in a single column and laid out as you indicated you should be able to split the single column into 4 columns using the | as the customer delimiter.
Hope this helps point you in a direction that will help.
Thanks
Randy
0 -
Hi colinr,
There are a couple of ways to do this. This can't be done in Beast Mode because it doesn't support the instr function looking from right to left.
- MySQL Flow (this is the easiest option)
trim(right(`Text`, instr(reverse(`Text`), ' | ')))
- The "text" field would be your text field. I added the trim in there because I couldn't remember if this would leave space padding on the left or not.
- ETL Flow
- Here is the whole flow. I assume you have a limit on how many texts are delimited in your field. In this example I put 6 as the max, but you can easily just add more.
- Input Data Set
- Split Column on Delimiter = " | "
- Collapse Columns to put the split columns in a row
- String Operation to trim spaces from the text field
- Filter rows to text field is not null
- Set column type. Set the rank to whole number
- Group by to get the max rank
- Inner Join back on itself to only pull through max ranks
- Select statement to rename fields and drop off some
- Here is the json code from my example flow. If you copy it, then click into some blank space on the etl, it should paste right in there.
{"contentType":"domo/dataflow-actions","data":[{"name":"test","id":"7ad5dc70-db63-4bba-8ca4-62f08d7e8073","type":"LoadFromVault","gui":{"x":24,"y":48},"dependsOn":[],"removeByDefault":false,"dataSourceId":"48368a1c-3227-436b-8e21-90088fd39c14","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0},{"name":"Split Column","id":"f90acc6d-de26-4777-a826-943a1576fadc","type":"SplitColumnAction","gui":{"x":144,"y":48},"dependsOn":["7ad5dc70-db63-4bba-8ca4-62f08d7e8073"],"removeByDefault":false,"sourceColumn":"Text","delimiterType":"CUSTOM","delimiter":" | ","useRegex":false,"additions":[{"name":"1","dataType":"STRING"},{"name":"2","dataType":"STRING"},{"name":"3","dataType":"STRING"},{"name":"4","dataType":"STRING"},{"name":"5","dataType":"STRING"},{"name":"6","dataType":"STRING"}]},{"name":"Collapse Columns","id":"54ec4815-c991-4b0d-b456-44349e7c3965","type":"Normalizer","gui":{"x":276,"y":48},"dependsOn":["f90acc6d-de26-4777-a826-943a1576fadc"],"removeByDefault":false,"typefield":"Rank","fields":[{"sourceField":"1","typefieldValue":"1","destField":"New Text"},{"sourceField":"2","typefieldValue":"2","destField":"New Text"},{"sourceField":"3","typefieldValue":"3","destField":"New Text"},{"sourceField":"4","typefieldValue":"4","destField":"New Text"},{"sourceField":"5","typefieldValue":"5","destField":"New Text"},{"sourceField":"6","typefieldValue":"6","destField":"New Text"}]},{"name":"String Operations","id":"3d67df50-8d34-49c3-b0d9-9fec7b018d6e","type":"StringCalculator","gui":{"x":408,"y":48},"dependsOn":["54ec4815-c991-4b0d-b456-44349e7c3965"],"removeByDefault":false,"calculations":[{"calcType":"TRIM","constantB":{"value":"BOTH","type":"STRING"},"fieldA":"New Text","fieldName":"Trimmed Text"}]},{"name":"Filter Rows","id":"276293e4-d8d5-4b92-870d-c1764501ba91","type":"Filter","gui":{"x":528,"y":48},"dependsOn":["3d67df50-8d34-49c3-b0d9-9fec7b018d6e"],"removeByDefault":false,"filterList":[{"leftField":"Trimmed Text","operator":"NN","andFilterList":[]}]},{"name":"Set Column Type","id":"ad71912a-9e28-4475-b461-0b9c251bb183","type":"Metadata","gui":{"x":60,"y":192},"dependsOn":["276293e4-d8d5-4b92-870d-c1764501ba91"],"removeByDefault":false,"fields":[{"name":"Rank","type":"LONG"}]},{"name":"Group By","id":"9128dfbe-ab36-424a-90c3-3ef1a1e4c258","type":"GroupBy","gui":{"x":192,"y":312},"dependsOn":["ad71912a-9e28-4475-b461-0b9c251bb183"],"removeByDefault":false,"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"ID"}],"fields":[{"name":"Rank","source":"Rank","type":"MAX"}]},{"type":"MergeJoin","name":"Join Data","id":"c32d5a7d-ccfa-40f2-91d7-60afaa50f825","gui":{"x":288,"y":192},"dependsOn":["ad71912a-9e28-4475-b461-0b9c251bb183","9128dfbe-ab36-424a-90c3-3ef1a1e4c258"],"joinType":"INNER","step1":"ad71912a-9e28-4475-b461-0b9c251bb183","step2":"9128dfbe-ab36-424a-90c3-3ef1a1e4c258","keys1":["ID","Rank"],"keys2":["ID","Rank"]},{"name":"Select Columns","id":"904e7e19-e979-47ac-8968-2e0b25c5360a","type":"SelectValues","gui":{"x":396,"y":192},"dependsOn":["c32d5a7d-ccfa-40f2-91d7-60afaa50f825"],"removeByDefault":false,"fields":[{"name":"ID"},{"name":"Trimmed Text","rename":"Text"},{"name":"Rank"}]},{"name":"test output","id":"b3cd72e8-8090-40f7-b22d-718e19bd6a9d","type":"PublishToVault","gui":{"x":528,"y":192},"dependsOn":["904e7e19-e979-47ac-8968-2e0b25c5360a"],"removeByDefault":false,"dataSource":{"name":"test output"}}]}
I couldn't figure out how to insert photos so I've attached them in order.
I hope this helps!
1 - MySQL Flow (this is the easiest option)
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive