8 Replies Latest reply on Nov 7, 2018 12:37 PM by Erin Gehn

    Parsing without SPLIT enabled

    Erin Gehn

      Hello~

      I have a few fields that require the SPLIT function. However, I'm working through a database connection that does not support custom splitting/splitting in the datasource window nor the split function in a calculated field.

       

      Is there another way I could pars a string of unknown length?

      For example:

      Fields I have-    "afdoak.akosdf_yes"   AND "alkjdkkkkksfio alskdfff yep"

      Fields I need-   "akosdf"                       AND "alskdfff"

       

      The split function could easily accomplish both tasks:

      SPLIT([field 1], ".",2)                        AND  SPLIT([field 2]," ",2)

       

      I have considered using MID, but for that to work, I would need to know the varying amount of characters within all pars phrase.

       

      Thoughts??

        • 1. Re: Parsing without SPLIT enabled
          Deepak Rai

          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

          Thanks

          Deepak

          1 of 1 people found this helpful
          • 2. Re: Parsing without SPLIT enabled
            Joe Oppelt

            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.

             

            String Functions

             

            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], "_")

            END

             

            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)

            ELSEIF ...

             

            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.)

            1 of 1 people found this helpful
            • 3. Re: Parsing without SPLIT enabled
              Joe Oppelt

              Oh, I like Deepak's suggestion od replacing various separators to just one thing.  Then all the ELSEIFs in my suggestion would be unnecessary.

              • 4. Re: Parsing without SPLIT enabled
                Matthew Hefferon

                I'd use REGEX for this. I've attached an example workbook.

                 

                Screen Shot 2018-10-19 at 11.34.09 AM.png

                1 of 1 people found this helpful
                • 5. Re: Parsing without SPLIT enabled
                  Erin Gehn

                  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.

                   

                  • 6. Re: Parsing without SPLIT enabled
                    Erin Gehn

                    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.

                     

                     

                    Thank you!!

                    • 7. Re: Parsing without SPLIT enabled
                      Ombir Rathee

                      Use the below calculated field. Replace Data with your field name.

                       

                      TRIM(MID(REPLACE(REPLACE(REPLACE([Data],"."," "),"_"," ")," ",SPACE(200)),150,100))

                      2 of 2 people found this helpful
                      • 8. Re: Parsing without SPLIT enabled
                        Erin Gehn

                        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!!