6

I have a column of strings that look like this:

Target Host: dcmxxxxxxc032.erc.nam.fm.com Target Name: dxxxxxxgsc047.erc.nam.fm.com Filesystem /u01 has 4.98% available space - fallen below warning (20) or critical (5) threshold.

The column name is [Description]

The substring I would like returned is (dxxxxxxgsc047.erc.nam.fm.com)

The only consistency in this data is that the desired string occurs between the 5th and 6th occurrences of spaces " " in the string, and after the phrase "Target Name: " The length of the substring varies, but it always ends in another " ", hence my attempt to grab the substring between the 5th and 6th spaces.

I have tried 

MID([Description],((FIND([Description],"Target Name: "))+13),FIND([Description]," ",((FIND([Description],"Target Name"))+14)))

But that does not work.

(Edit: We use Tableau 8.2, the Tableau 9 only functions can't be part of the solution, thanks though!)

Thank you in advance for your help.

Travis
  • 371
  • 4
  • 18
  • 1
    It's so hard to find such well structured questions on Tableau thread, that I must congratulate you! May all questions be as good as yours! – Inox Apr 24 '15 at 14:03
  • Haha thanks, if someone is going to take the time to help me out, I try to make it as painless as possible for them. – Travis Apr 27 '15 at 14:55
  • This is simple if your version of Tableau has the Split function (I don't remember when that arrived) as this will split a string on a defined character and all you to extract the nth substring in the result. – matt_black Feb 15 '19 at 15:05

4 Answers4

9

In Tableau 9 you can use regular expressions in formulas, it makes the task simpler:

REGEXP_EXTRACT([Description], "Target Name: (.*?) ")

Alternatively in Tableau 9 you can use the new FINDNTH function:

MID(
     [Description],
     FINDNTH([Description]," ", 5) + 1, 
     FINDNTH([Description]," ", 6) - FINDNTH([Description]," ", 5) - 1
   )

Prior to Tableau 9 you'd have to use string manipulation methods similar to what you've tried, just need to be very careful with arithmetic and providing the right arguments (the third argument in MID is length, not index of the end character, so we need to subtract the index of the start character):

MID(
   [Description]
   , FIND([Description], "Target Name:") + 13
   , FIND([Description], " ", FIND([Description], "Target Name:") + 15)
     - (FIND([Description], "Target Name:") + 13)
)
Max Galkin
  • 16,264
  • 9
  • 63
  • 108
  • 1
    I don't think FINDNTH() was available until Tableau 9 either. So that part of the answer is not completely right. – Alex Blakemore Apr 24 '15 at 04:39
  • If your DB supports REGEX then Max's answer is the best way to go. – e h Apr 24 '15 at 09:12
  • 1
    Yeah that's correct, we use 8.2 here, I did see that was coming in 9. Any ideas on how to do it without the functions only available in 9? – Travis Apr 24 '15 at 13:38
  • Didn't know that FINDTH function, pretty handy. Thanks! – Inox Apr 24 '15 at 13:50
  • @AlexBlakemore @Travis -- thanks for correcting me, `FINDNTH` is indeed a new function in 9.0 as well as `REGEXP_EXTRACT`. I've updated the answer with a formula that works in 8.2. Hope that helps. – Max Galkin Apr 24 '15 at 15:27
  • @Travis you mean some input rows have different format? in some cases it might really be easier to edit those rows in the data source... :) so that they all have the same format... – Max Galkin Apr 24 '15 at 17:17
  • No I was screwing it up with something I was adding to the front of it, your code works perfectly. One part I don't quite understand, the last section `FIND([Description], "Target Name:") + 15) - (FIND([Description], "Target Name:") + 13)` Wouldn't that return 2 every time? – Travis Apr 24 '15 at 17:40
  • @Travis no, if you read carefully, there are nested FIND functions... basically I'm trying to FIND the first space following the space after "Target Name"... if that makes sense – Max Galkin Apr 25 '15 at 05:35
2

Well, you need to find "Target name: " and then the " " after it, not so hard. I'll split in 3 fields just to be more clear (you can mix everything in a single field). BTW, you were in the right direction, but the last field on MID() should be the string length, not the char position

[start]:

FIND([Description],"Target name: ")+13

[end]:

FIND([Description]," ",[start])

And finally what you need:

MID([Description],[start]+1,[end]-[start]-1)

This should do. If you want to pursue the 5th and 6th " " approach, I would recommend you to find each of the " " until the 6th.

[1st]:

FIND([Description], " ")

[2nd]:

FIND([Description], " ",[1st] + 1)

And so on. Then:

MID([Description],[5th]+1,[6th]-[5th]-1)
Inox
  • 2,225
  • 3
  • 11
  • 25
1

A simple solution -

SPLIT( [Description], " ", 3 )

This returns a substring from the Description string, using the space delimiter character to divide the string into a sequence of tokens.

The string is interpreted as an alternating sequence of delimiters and tokens. So for the string abc-defgh-i-jkl, where the delimiter character is ‘-‘, the tokens are abc, defgh, i and jlk. Think of these as tokens 1 through 4. SPLIT returns the token corresponding to the token number. When the token number is positive, tokens are counted starting from the left end of the string; when the token number is negative, tokens are counted starting from the right. - Tableau String Functions

Bob
  • 320
  • 2
  • 8
0

I don't know Tableau, but perhaps something like this?

MID(
    MID([Description], FIND([Description],"Target Name: ") + 13, 50),
    1,
    FIND(MID([Description], FIND([Description],"Target Name: ") + 13, 50), " ")
)
shawnt00
  • 12,316
  • 3
  • 14
  • 19
  • The lengths of the strings I want aren't static, they go anywhere from 8 to 40 characters. I need to grab everything between the spaces. Thanks though. – Travis Apr 24 '15 at 13:38
  • Just change the 50 to be wide enough to ensure you grab enough the next space following "Target Name: ". But 50 is already big enough if the range is from 8 to 40. Why won't that work? – shawnt00 Apr 24 '15 at 15:00
  • @Max, just because I'm curious. What's wrong with the idea I posted? – shawnt00 Apr 24 '15 at 19:53
  • that probably would work, ill check it out when after it's 100% working I start trying to make it more effecient – Travis Apr 27 '15 at 14:54