'Split-then-Pivot' is a common data prep task --
and a very 'uneasy' one with Tableau Desktop alone.
Split-n-Pivot is easy with Tableau Prep,
though some extra steps would be required.
The fine point is an ambiguous number of the resulting Split Columns.
It could be max-capped by adding a 'Scaffold' (1-row) dataset to the mix.
Please find the attached as an example.
Hope it could help.
The thing is I already tried that Split and it does not detect the "\n". The source is an excel file, I forgot to clarify, that has line breaks within the cells. The source looks something like this:
Order Requests Date Owner Extra ptv-190288 1/04/2018 Gomez Angel No ptv-198298
3/05/2018 Damien Perez Yes ptv-123324
5/06/2018 Camilo Gonz No
When ingested into Data Prep Tableau data prep adds the "\n". Its there where I cant find the way to split by "line break".
By the way, the could be 1 to 100 orders per date, should I creat a Trim for each of the 100 just in case?
Oh, I see.
Could you please share
a sample Excel file for me
to try some workaround(s)?
1 of 1 people found this helpful
Add a cleanse step off of your data source. In that step first add a calculated field “New Order”. Formula: REPLACE([your order column name], CHAR(10), “;”). Then perform a manual split on the “New Order” column. Separator is ; and select ALL for number of columns.
Add a Pivot step to your flow. Select all the split columns (e.g. New Order Split1, New Order Split2, etc).
Step 1a Clean - Add Calculated Field, New Order Request: REPLACE([Order Requests],CHAR(10),";")
Step 1b Clean - Split, manual, use ; as separator, select ALL
Step 2 Pivot - move the split fields to the pivot (e.g. Split 1, Spilt 2, ...)
Step 3 Clean - remove the extra fields of data
Crispin Data Prep.tfl.zip 2.8 KB
As Garth suggested, the cleansing tasks
should be added after importing the Excel data.
I'm using REGEXP_REPLACE() variant of the calc,
everything else is just the same as in my previous flow.
Please find the attached w/mods.
Not sure what is going on. If you can send a portion of the data set that is causing problems I will take a look at it.
I mocked up some data with 10 line breaks per cell and it split as intended with the suggested method I posted as well as the one Yuri posted. I also tried to trick the split with extra spaces after the order numbers since you used a space as the separator in your replace step, it still split into 10 columns as intended.
I would suggest one thing, even though it doesn't impact this case, it is better to use a character when you do a replace that you intended on splitting. You may find some data sets have spaces between words or numbers that will get picked up accidentally and then cause problems in the split.