-
1. Re: Tableau Prep: Concatenating many to one into new calculated field
Rodrigo Calloni Dec 28, 2018 10:32 AM (in response to Martin Sellers)Hello Martin
Here is one option that may work:
1- Split the Full Schedule field using the ) as delimiter to the split
2 - Add a Pivot Step and pivot all split fields. This will create one column that contains every daily schedule and they will be related to each schedule_code
3 - You may have to delete some empty rows that may result of the pivot.
Note: If you could copy and paste the two rows from your dataset into an Excel/CSV file and share I could try with the data and see if that really works.
Rodrigo
-
2. Re: Tableau Prep: Concatenating many to one into new calculated field
Joshua Milligan Dec 28, 2018 2:06 PM (in response to Martin Sellers)Martin,
This is a tough one without an easy answer. Jonathan Drummey created this idea for Tableau Desktop, though ideally it would apply to Prep as well: https://community.tableau.com/ideas/4538
Here are some thoughts:
- If your data source is SQL Server, you should be able to use Custom SQL in Tableau Prep to meet your goal (see sql - Multiple rows to one comma-separated value - Stack Overflow for a technique). There are likely ways to accomplish this with other relational databases that allow for Custom SQL. You could use a Custom SQL Input and then join the results to your main dataset.
- Using a few Aggregate steps (or duplicates of the Schedule Code field in a single Aggregate step), you can get the MIN, MAX, and Count Distinct schedule codes per unique schedule. That would allow you to join back into the flow, and employ some logic. Where the distinct count was 1, you could keep the value. Where it's 2 you can have Min + "," + Max. Where it's 3 or more you can have Min + "," + Max + " and " + STR(Count - 2) + " others."
- Consider voting for this idea, which would provide a possible solution: https://community.tableau.com/ideas/8732
- If you are going to visualize this data in Tableau, consider leaving your data structure as is. You can build a comma-delimited list with a table calculation or even just put Schedule on Rows and Schedule Code on Text in the Marks card. Filtering and a few other things will be easier with your existing structure as opposed to building a delimited list.
Hope that helps!
Joshua