2 Replies Latest reply on Jul 20, 2016 11:23 PM by Jacron Pagel

    How to split a delimited field then unpivot on it

    Jacron Pagel

      Hello,

       

      I have a csv data source that look like this:

       

      item_uuidAlert Summary
      6e4f7abd-ea46-4c29-b0c2-f3e5d510b7fcABC|DEF|GHI
      6e58daef-e569-480c-a61a-b5de2c87b4c9ABC|JKL
      6e82973a-d332-482d-a55f-8f7f238140d6DEF|ABC|XYZ

       

      I want to do various analysis on this data, e.g. analysing the number of item_uuid for each Alert Summary (ABC, DEF etc). I can have zero, one or unlimited Alert Summary per item_uuid, and I do not know ahead of time what values they may take. So I've concluded that the best way is to split + unpivot the data into the form:

      item_uuidAlert Summary
      6e4f7abd-ea46-4c29-b0c2-f3e5d510b7fcABC
      6e4f7abd-ea46-4c29-b0c2-f3e5d510b7fcDEF
      6e4f7abd-ea46-4c29-b0c2-f3e5d510b7fcGHI
      6e58daef-e569-480c-a61a-b5de2c87b4c9ABC

      etc

       

      And then the worksheets are simple to create. (It's actually worse than that, the real separator is ")(", but I simplify for brevity...)

      In SQL, I would use a CTE or cross apply split along the pipe and unpivot. But CTEs do not work in custom SQL and I can't get the SQL "CROSS APPLY String.nodes ('/M')" method working either. Sadly I cannot change the output format or do pre-processing for various reasons.

       

      So I'm coming to you all for help How can I split and unpivot a csv along arbitrary values within tableau?

       

      Thanks,

      J