1 of 1 people found this helpful
You need to use MID and FINDNTH. but Before that use Replace to Convert "." to " " or Vice Versa. That will enable you to deal with only one Delimiter. Once That is Done, use Findnth for " ". This would give you blank space positions for entire field and definitely it will be variable for all Rows. and I see you have second delimiter in the field also. Use Replace again to change it to Blank Space. Now your desired substring stands between two blank Spaces. Use again FINDNTH to get position of 2nd Blank after your Substring. Once you have got start and end positions of your desired Substring, now to get it use
MID(Your String, (Start from position got from FIRST FINDNTH-1), (END at position got from second FINDNTH-1)
Weird but Try
1 of 1 people found this helpful
Probably REGEX function can do things for you here. I don't know enough about it though. I just use various string functions to do stuff like this.
It looks like you have to know what possible "separators" you want to look for. From your example the period, space, and underscore are the ones your example use. I'll bet there are more.
give the limited examples you gave, I would do
// this is [Calc A]
IF FIND([your string], " ") > 0 THEN or FIND([your string], " ")
ELSEIF FIND([your string], ".") > 0 THEN FIND([your string], ".")
ELSEIF FIND([your string], "_") > 0 then FIND([your string], "_")
That gives you the first separator.
To find the second separator you would do the same except you would give a start position for the FIND
IF FIND([your string], " ", [Calc A]+1) > 0 THEN or FIND([your string], " ",[Calc A]+1)
And then your substring would be MID of your string from CalcA+1 through CalcB-1.
And if you wanted, all that could be embedded in one giant calc. It would be ugly to manage, but some people do it that way. (I would compartmentalize.)
I don't know if your substring would ever start with position=1 (no separator in the front). If so, you would have to figure out how to identify and deal with that. Likewise what you might have to do if the target substring runs to the end of [your string]. (In my opinion, compartmentalized calcs makes it easier to deal with conditions like that.)
Oh, I like Deepak's suggestion od replacing various separators to just one thing. Then all the ELSEIFs in my suggestion would be unnecessary.
Unfortunately, I don't have regex options from this version of Tableau/connection with ODBC.
I tried a regex function before lunch and I got an error message saying that the function was not recognized.
This may have to be what I go with. (I'm still testing it out).
There aren't many options within the version of Tableau/database connection type that this project is using.
2 of 2 people found this helpful
Use the below calculated field. Replace Data with your field name.
TRIM(MID(REPLACE(REPLACE(REPLACE([Data],"."," "),"_"," ")," ",SPACE(200)),150,100))
Thank you all for your suggestions!
In the end, all the suggestions provided did work within my 2018.2 connection!! However, the client's connection (Tableau 10.5 and an ODBC connection) was only compatible with Joe's answer. Here was the final function::
Mid([Scheduling Dtl Txt], ((FIND([Scheduling Dtl Txt], " "))+1) , ((FIND([Scheduling Dtl Txt], " ", (FIND([Scheduling Dtl Txt], " ")+1))-1)))
Thank you again!!