0 votes
1 view
in BI by (17.6k points)

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. 

1 Answer

0 votes
by (47.2k points)
  • You can use regular expressions in formulas, it makes the task simpler in Tableau 9 :

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

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



     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 MIDis length, not index of the end character, so we need to subtract the index of the start character):



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

   , FIND([Description], " ", FIND([Description], "Target Name:") + 15)

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


Related questions

0 votes
1 answer
0 votes
1 answer
asked Aug 27, 2019 in BI by Vaibhav Ameta (17.6k points)
+2 votes
3 answers
0 votes
1 answer
asked Feb 3, 2020 in BI by Vaibhav Ameta (17.6k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !