Edit String in Beastmode or ETL

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.

1. 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.
2. ETL Flow
   1. 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.
   2. Input Data Set
   3. Split Column on Delimiter = " | "
   4. Collapse Columns to put the split columns in a row
   5. String Operation to trim spaces from the text field
   6. Filter rows to text field is not null
   7. Set column type. Set the rank to whole number
   8. Group by to get the max rank
   9. Inner Join back on itself to only pull through max ranks
   10. Select statement to rename fields and drop off some
   11. 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.

   12. {"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!