Getting the last field of a character-separated string

froghunter · November 2022

I have a field that has an odd structure. It looks like this:

asdfas\\qwerqwer\\..\\vcbnvcn

To be clear, the .. means that there's an arbitrary number of double-slash separated strings, not a literal double dot. Also, each field can be of arbitrary length. I need the last field. In the example, I need to return vcbnvcn. I've attempted both ETL and Beast Mode methods. The ETL method seems to require splitting into lots of extra columns, and then for each row somehow getting the last one that's populated. The Beast Mode method seems to spin off into far more complex methods than I understand in my very new state of Domo learning.

I guess I figured there would be something straightforward, like the Python:

<string>.split('\\')[-1]

Is there a way to do this? Thank you.

DashboardDude · November 2022

Hi @froghunter ,

Here are a couple ways to do it. If the last value is all the same length, you can use the RIGHT feature in text. If not, then you need to do the split to columns/ delimiting trick you were doing.

Here's a video that I hope is helpful: https://www.loom.com/share/3f7c949e382a442a903a32e9005f168e

Good luck,

John Le of Dashboard Dudes

You're only one dashboard away. More video solutions at: https://www.dashboarddudes.com/pantry

MarkSnodgrass · November 2022

@froghunter I did something similar where I am looking at a sentence and breaking it up into individual words and don't know how many spaces are in each sentence. This video walks you through how to do it. You would need to look for \\ instead of spaces in my example. You will also need to do a group by at the end of your process and look for the max row to find the last value in your string. Hopefully, that will make sense after watching the video.

https://youtu.be/sXOTfUnOUVU

froghunter · November 2022

@MarkSnodgrass thank you for the video. I'm still working on it. I ended up getting what I needed with the other suggested method, but this has enough interesting ideas that I'd like to keep at it.

@DashboardDude, thank you. This worked very well, and with it laid out like you did it became pretty straightforward. I didn't realize at first that you'd made a custom video just for this, until I saw my name. I really appreciate the effort and great help. You made a comment in the course of the video about rolling all of the separate case statements into a single one, so I got ambitious and gave that a try. Here's the idea of what I came up with:

CASE

WHEN `Split 04` IS NOT NULL THEN `Split 04`

WHEN `Split 04` IS NULL AND `Split 03` IS NOT NULL THEN `Split 03`

WHEN `Split 03` IS NULL AND `Split 02` IS NOT NULL THEN `Split 02`

WHEN `Split 02` IS NULL AND `Split 01` IS NOT NULL THEN `Split 01`

ELSE

'We got a problem!'

END

Reviewing the first thousand or so results, that seems to work. I'd be curious if you see any gotchas.

Again, thanks for all the help.

DashboardDude · November 2022

@froghunter

Yeah custom videos just make it easier than doing some examples sometime. I love the "We Got a Problem!"

Yeah your beast mode logic is good! You will soon be AllAmphibianHunter at this pace

Getting the last field of a character-separated string

Answers

Categories