CODEWITHSUNDEEP
tableau-api
Travis
Travis

Reputation: 420

Having issues with a substring function in Tableau

I am trying to break a string up into substrings based on the position of a repeating set of characters.

The source string [UPDATES] looks like this, the number of characters between the repeating portions varies wildly.

"04/24/15 15:12:54 (PZPJ3F): Task update.       04/24/15 15:12:54 (PZPJ3F): Task update. 04/22/15 15:17:13 (SZGQ3T): updated due date prior to global problem 04/22/15 12:28:09 (PZPJ3F): Task updates."

I am trying to break them up into separate substrings so that I can display them side by side as separate columns as below

Column1 = |04/24/15 15:12:54 (PZPJ3F): Task update.
Column2 = |04/24/15 15:12:54 (PZPJ3F): Task update.
Column3 = |04/22/15 15:17:13 (SZGQ3T): updated due date prior to global problem|

I got the first portion to work with 

LEFT([UPDATES],FIND([UPDATES],"): ",28)-27)

But my attempts at using FIND to locate the next occurrence of "): " and use it to begin a MID are not working, specifically where I try to end them using a FIND function.

IF [Mark1]>0 THEN MID([UPDATES],[Mark1]-25,[Mark2])
ELSE ""
END

Where Mark1 is

FLOAT(FIND([UPDATES],"): ",(FIND([UPDATES],"): ")+1)))

and Mark2 is

FLOAT(FIND([UPDATES],") ",[Mark1]+1))

I really went down the rabbit hole at the end of my attempt.

I am using Tableau 8.2, so Tableau 9 functions aren't an option (looking forward to FIND Nth!

Thanks in advance.

Upvotes: 1

Views: 1992

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11919

The key is that find() takes a second optional argument as a start position.

So in Tableau 8.2, I would write a simple calc to find the position of the first separator. Then reference that calculated field twice in your final calculated fields to yield the length of the first substring and the starting point of the second one.

Separating out substrings is painful prior to Tableau 9. Extracting the first in a list isn't bad, getting the second is clumsy and after that it gets pretty ugly.

Best approach is to upgrade to version 9 or do some preprocessing to pull out the substrings.

Upvotes: 2

Related Questions