3 Replies Latest reply on Sep 27, 2017 3:33 PM by Hari Ankem

    Trouble with excess data in HTML to string conversion

    Eli Peterson

      Hello, this is my first post/question so I will do my best to explain my situation and then ask my question.

       

      I am extracting data from an ODBC, but one of the fields (Description) is HTML, and the field is free text.  In the SQL query from the database, I converted the field into a string (because it wasn't populating as a field in Tableau) using the following...

       

      CAST(CYCLE.CY_COMMENT as varchar(300)) as "Description"

       

      Now when I access the data in Tableau the field populates in the following formats...

       

      "<html>

      <body>

      <div align=""left"" style=""min-height: 9pt; ""><font face=""Arial""><span style=""font-size:8pt"">APO Cloud Migration </span></font></div>

      <div align=""left"" style=""min-height: 9pt; ""></div>

      </body>

      </html>"

       

      or as

       

      "<html>

      <body>

      <div align=""left"" style=""min-height: 9pt; ""><font face=""Arial""><span style=""font-size:8pt"">APO Cloud Migration</span></font></div>

      </body>

      </html>"

       

      I have used the following Calculated Field in Tableau to Extract my desired text (seen in red above)

       

      TRIM(REGEXP_REPLACE([Description],'<.*?>',""))

       

      The results of using this are great!  It leaves only the text in red and trims out the extra spaces.

       

      The problem I now have is that I am receiving multiple values in the rows when building tables with the same text and there are some extra carriage returns in the results. 

       

      Example of my current table:

      APO Cloud Migration       50

      APO Cloud Migration..     12

      APO Cloud Migration       38

      AUG_TEC                        90

      JUL_BIZ..                           1

      JUL_BIZ                             4

       

      The results below are respective the first bold set results above using my equation in blue, notice the lines before and after the desired text in quotes.

       

      "

       

       

      APO Cloud Migration

       

       

      "

       

      and

       

      "

       

       

      APO Cloud Migration

       

       

       

       

      "

       

      How do I combine the desired text results all together in a single row when building a table?  Should I edit the HTML in the SQL query, if so how?

       

      Example:

      APO Cloud Migration      100

      AUG_TEC                         90

      JUL_BIZ                              5

       

      I am a novice when it comes to Tableau and databases, so please try to be clear and simple when you are helping answer my question.  Thank you in advance!