0:00In previous versions of Tableau prep, prep
0:02did not observe the column ordering that
0:04you set up inside of the product.
0:07In 22.2, it does. Let's get stuck in.
0:10Here I've connected to a data source inside
0:12of Snowflake and essentially I've connected
0:15a sample data source.
0:16I've already got a data flow going with the
0:18summary table up.
0:20What you've typically done in the past to
0:22sort columns in this view is just to drag
0:25one of them
0:26and just wait for this cursor to sort of
0:27show you an area that you can place it in
0:30between a specific point and you drop it in
0:31.
0:32Now the problem in the past has been that
0:34when you then go and output this file to a
0:38particular file type, CSV, Excel, or to a
0:40database,
0:41that column ordering has not been observed.
0:44In 22.2, it is observed.
0:46That's something that's been asked for in
0:47the community feedback.
0:49In fact, if I go to the community forums, I
0:52'll just bring it up in another window here
0:54and put it in front of the screen,
0:55this should hopefully load this up.
0:58This was recommended three years ago by
1:00Jonathan Drummy, a Tableau visionary in the
1:03community.
1:04And essentially this has been voted 1.4
1:07thousand points.
1:09It's absolutely crazy. It's such an obvious
1:11thing as well.
1:12You know, just to just preserve the sorting
1:14that I give my data in the data structure.
1:17So now that this is actually coming out, it
1:19's great to see the Tableau listening.
1:21The cool thing about this is that you can
1:23do it in two ways.
1:25So let's just go back to this clean step
1:27number one over here.
1:29And if I go to the field summary view, you
1:32can also do it here.
1:34So if I just go and drag my phone list up,
1:39let's bring nation key to the very top and
1:41I'll put phone second.
1:43What we're going to do is we're going to
1:44output this to a CSV file, which will then
1:46open in Excel.
1:47So let's go ahead and do that.
1:50Let's actually just put it out to an Excel
1:51file straight away so I don't have to open
1:53it into anything.
1:54We'll just call this file.
1:58Create a new worksheet called file.
2:02Replace the entire data set every time we
2:04write this because we're going to open it
2:06more than once.
2:07And in terms of where to put this file, I'm
2:09going to put it inside of my recordings
2:12folder where I'm recording all the videos
2:13for this.
2:14So we'll put it in there.
2:16So we're going to go in the output folder
2:18and what we want to see is that nation key
2:20and phone key come up at the very top.
2:22You can see that's the order that I've just
2:24given it here.
2:25You can see it's also the order that I've
2:27got there.
2:28So we're going to see if that actually
2:29happens.
2:30Let's go ahead, hit run the flow.
2:32It will go out to the Excel file.
2:34While that is running, I'll just go ahead
2:36and get that folder open.
2:38So we will see that file turn up hopefully
2:40anytime soon.
2:42And yeah, we'll have a look at the actual
2:43file.
2:44In fact, this is a very large data set, so
2:46this might have been a very, very bad idea
2:48to do this, especially over Snowflake.
2:50So we'll wait and just let it finish.
2:53Maybe we'll hit the Excel row limit and we
2:54'll see what happens.
2:56So let's wait for this to finish and then
2:59we'll open it up.
3:02OK, it's finished.
3:03I did actually pause and restart this.
3:05I'll explain why in a second.
3:07It's gone to my default location,
3:09unfortunately.
3:10There it is in my data sources folder.
3:12So that's exactly where we'll go.
3:14We'll open up the file explorer.
3:15We go to my documents folder.
3:17We'll go to my Tableau repository, prep
3:22repository, data sources, output.xlx.
3:26I can even see from here that the sorting
3:28has been maintained because I can see the C
3:31Nation column there.
3:33And what I did is I filtered it down to
3:35just one row.
3:36And you can see that the sorting of the
3:38columns has indeed been observed.
3:41The two that I set there have come back.
3:44Now what I'm going to do is close this.
3:46We're going to change the sorting and we're
3:47going to run it again.
3:48It will hopefully overwrite that file.
3:50So let's go back in here.
3:52I think I actually changed this.
3:53Did I change this in the subsequent step?
3:55I think I did.
3:57I didn't change it in the subsequent step.
4:00I just changed it in here.
4:02So what we can do is go ahead and maybe put
4:04the address field at the top again.
4:07And we'll go to the output.
4:09You'll see that the address is now at the
4:10top.
4:11Hit Run Flow.
4:13It will overwrite the file.
4:15That's been done.
4:18Now there's something really interesting
4:19going on here because you can see that I've
4:21actually set address to be at the very top.
4:23But my output is still showing the Nation
4:25key, the old setup.
4:27So I think what might be going on here that
4:29it's caching something about the file
4:31because if I delete this and add it back in
4:37,
4:37I do see the updated sorting.
4:40So whenever I add a new output, the sorting
4:42actually persists.
4:43And if I just go ahead and output that to
4:46exactly the same Excel file,
4:49in this particular case, I think I called
4:50it a file.
4:51I wanted to literally overwrite the
4:53previous one, replace the data.
4:55Everything should be called output, output,
4:57output.
4:58Oh, interestingly, when we select that file
5:04, it pulls that previous sorting that we saw
5:10.
5:11It's not until I actually, if I give it a
5:13different file name, let's say file number
5:16two,
5:17create a new actually called file number
5:19two, then the new sorting persists.
5:23But if I name it after the original one,
5:28then it actually grabs the sorting that's
5:30already in that file.
5:32It seems it doesn't seem to grab it and
5:34then rearrange it.
5:36It doesn't sort of push what I've actually
5:39got here in the data.
5:41I've set it here to replace the data.
5:45Let's set create table and that might
5:46actually be the better thing to do.
5:49Okay, so that's, is that a knowledge gap
5:52for me? Probably is.
5:54If you look over here, the reason that wasn
5:55't actually working is because the setting
5:58here was not set to create a table every
6:01single time.
6:03I mistakenly assumed that replacing the
6:06data would do the same thing,
6:09but actually replace the data only creates
6:12the table if it doesn't exist.
6:14If it already exists, it doesn't create the
6:16table, it keeps the columns as they are and
6:19fills the data in,
6:20essentially drops the table and puts the
6:22data back in.
6:24Creating the table completely deletes
6:26everything and then puts it back in.
6:28So that's why the sorting works a little
6:30bit better.
6:31So I was going to say the sorting doesn't
6:32stick when you change the output,
6:34but you just need to make sure that you got
6:35this to create table for it to overwrite
6:37the existing sorting that was previously
6:40selected.
6:41Okay, so that's a really nice quality of
6:42life improvement.
6:44Now, the only file types this works with, I
6:46'm going to check my notes here, is Excel,
6:48CSV, Hyper.
6:50And then in terms of databases, Google, Big
6:52Query, Redshift, Snowflake, and then of
6:54course published data sources on Tableau
6:56Cloud or Tableau Server.
6:58So it's a new feature, but of course it
7:00doesn't work with absolutely everything.
7:03Microsoft SQL Server is not in that list,
7:04Oracle is not in that list.
7:06So hopefully it comes to more and more
7:07databases over time as they start to roll
7:09this out.
7:10But nonetheless, this is quite nice to have
7:12this happen, that it persists the column
7:15sorting for you inside of the product.
7:19Thanks for watching and I'll catch you in
7:21the next 2020.2 video.
7:23Bye.
7:24[silence]
7:30[ Silence ]
Tableau | Data prep