Edit String in Beastmode or ETL

colinr · October 2019

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.

Randyb · October 2019

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

Figauro · October 2019

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!

Edit String in Beastmode or ETL

Comments

Categories