9. Value Operations - Split Values Video - video Dailymotion

eTrainerbox

Watch 9. Value Operations - Split Values Video - eTrainerbox on Dailymotion

Transcript

00:00Let's talk about split value value operations.

00:04In Tableau Prep, you're able to split text based on values on automatic or custom defined delimiters.

00:11Splitting can be a valuable tool that can help derive new calculations and values to extract data from our fields.

00:19To split values, go to your More Options menu, choose Split Values, and then you'll have the option between Automatic and Custom Split.

00:27An automatic split will derive the split by using common delimiters.

00:31Tableau Prep will automatically decide which delimiter to split by and how many splits should be shown.

00:38If Tableau Prep doesn't get it right, you can always go ahead and do a custom split.

00:43A custom split will allow you to define the delimiter and the number of columns for the split.

00:48Now it should be noted that if you're doing an automatic or a custom split, you're always going to get a calculation out of it.

00:56Now you can also create a calculated split by hand, but the format will be the same.

01:01So if you're doing an automatic, a custom, or a calculated split, you'll always get a calculation with a very similar structure to it.

01:08The calculation will be set up with the split function.

01:10You'll identify the field in question in the first part of your calculation.

01:14Then you'll call out which delimiter you're using.

01:17Here, in our example, we're using a dash.

01:19And then the third argument in the function is the number of columns that you want to split to.

01:24And then automatically your calculated split will have a trim around it to get rid of any extra spaces.

01:31If you've used Tableau Desktop at all, the split commands will be almost identical to those.

01:36But if you haven't, they are relatively straightforward.

01:39And we're going to be going through some examples in Tableau Prep right now.

01:43So let's hop in our flow and create some splits off of our own data sources.

01:47Alright, so we're back in Tableau Prep.

01:49Let's go to our plans for high school graduates clean step.

01:52Select it here.

01:53And then we're going to go to our school name column.

01:56And we're going to select more options.

01:58And for more options we'll go down to split values.

02:01And for this option, we're going to choose custom split first.

02:04And then we'll get into automatic splitting after.

02:07Let's go ahead and choose custom split.

02:09And we're going to call out our separator as a single dash.

02:13And we're going to want to pull in the first two fields.

02:16Now you'll see, even before we split the field here,

02:19that we have a dash separating this first part and the second part of our field.

02:25Now the first part is our district, spoiler,

02:28and the second part is the actual school name.

02:31So we want to break this out because we're going to be combining data sets

02:35and that district name is going to be useful,

02:37especially when we're getting into our district part of the flow.

02:42And if we ever want to combine these school level metrics with our district level metrics,

02:47we're going to need that district called out.

02:50So it's really important that we're going to be able to split this field

02:53so that we can aggregate these data sources together at the proper level.

02:57Let's go ahead and choose split and see what happens.

03:00So right away we can see that we've got a split in two places.

03:04We've got split one and split two.

03:05It's called out in the name.

03:07And when we look quickly, we can see our district names are coming out pretty clean.

03:12And our school names look good too.

03:14Now I know that this says school name, but it's really a concatenation of district and school.

03:20So we want to blow these out so that we can do those proper joins and unions and aggregations later in our flow.

03:27Now we'll get to renaming and cleaning these up a little bit in the next few lessons,

03:30but for now let's go to our next data sources in our flow and apply some more splits.

03:35Next we're going to go to our educator evaluation.

03:38Hit the clean step.

03:39We're going to go to school name again.

03:41So we're going to go over to more options, choose split values.

03:45And instead of a custom split, we're going to use automatic split and see how this turns out.

03:50Tableau Prep is pretty good at this as well as Tableau Desktop.

03:53So let's see if it gets it right.

03:55We'll hit automatic split and very quickly we get our split out here.

04:00And yes, it is using that common delimiter, the dash that we had called out manually in the last step.

04:06So this is looking good.

04:08Again, we'll clean these names and fields up a little bit in the next few lessons.

04:12Let's go over to our SAT performance clean step.

04:15We're going to click on school name, more options, split values, and automatic split again.

04:22These are the same types of fields, right?

04:24They're all our school names and we're blowing them out to get district and school separated.

04:29So we can perform the same types of actions on each.

04:33Now let's go down to teacher data, do this one more time.

04:36We're going to hit our more options on our school name, split, automatic split.

04:41Alright, looking good.

04:43And that's it for now on our splits.

04:45And I know that we did these same actions a few times here.

04:49And again, we could do these a little more efficiently by unioning first,

04:53but we always run the risk of unioning fields that aren't fully cleaned up.

04:57And then we may not be able to clean them in the same way after we've done that

05:01because some of these data sources are not exactly like the others.

05:04So we may require different types of cleaning for each source before we do the combination.

05:09So we're being very safe here.

05:11We're taking it one source at a time, cleaning it fully,

05:14and then we're going to be doing our unions and joins.

05:17And everything should come out really clean if we do that right.

05:20Next up we'll be looking at how to directly edit our values from our profile pane.

05:25ульf

05:27003

05:32003

05:33004

05:340033

05:370023

05:38003

05:390023

05:40003

05:4000 aims

05:41003

05:420066

05:43007

05:440075

05:45006

9. Value Operations - Split Values Video

Category

Transcript

Recommended