How Do I Write Custom SQL In Tableau?

in this video i'm going to show you how to write custom sql in tableau let's go [Music] hello here we are again for another tableau video i'm super happy because i just got my morning routine cookies and cream from starbucks this is not a um commercial for starbucks by the way i just really like starbucks and there's like a drive-through right down the street from me so i have just been having it all the time while we've been in lockdown and it has been sweet all right so here we go i have a data set here and this is you probably recognize this this is the superstore data set and there's a lot of fields in here but we're really only going to use two for this example and let me just make this kind of a bit more legible so we can kind of see what's going on and the ones we're really interested in are category and quantity okay and the only reason i've chosen that is just because it's really simple so what we need to do to do this test for the custom sql is i'm going to take this data put it into an a microsoft access database um but it basically works the same for any sql based uh system that you're going to be connecting to with tableau so if i take this thought out what i'm going to do is i'm going to do a simple pivot so i'm going to take the whole thing and if you don't know pivot tables basically what i'm going to do is i'm going to take these two fields that i've added and you know maybe i'll put it next to each other just to make it easier so i'm not constantly scrolling let's go here and what i want to do is i want to add up all the quantities but group them by uh the category so that's easily easy enough so let's go insert i'm gonna do a pivot table and i'm going to add that in so let me just move my face over here for a second so we're going to bring in category and we're going to bring in quantity okay now let's say in tableau i want to connect to this result i don't want to connect to the master set and why would that be useful well a lot of the times when we're connecting to massive databases well they're massive so you don't want to have to bring it in and then do all the filtering in tableau because computationally it's very expensive so if you have a million rows but you only need you know 10 000 of them you don't want to bring the million into tableau and then filter it because it's just it's just inefficient it's not a good way to do it so a lot of the times what we want to do is we want to do some pre-manipulation or pre-restructuring of the data so we only grab what we need you can do that in the respective system for example now that i've brought that data in here right the same data i can write a query in here to achieve the exact same thing as i have here which i can do right now so if i do a query and let's just move my face again if i do a query like this okay category and let's get quantity and we're going to make this a sum okay i get the exact same results so i can save this query and go um you know quantity extraction all right let's call it that i can connect directly to this query or i can connect directly to the table from tableau so let's do that let me open up some open up my tableau give me time to sip oh yeah okay so now we're going to connect to this access database so we're going to go microsoft access now from memory tableau public you won't be able to do this because i think microsoft access is not part of your choice but i can't remember exactly so microsoft access i'm going to look for that file okay custom sql i'm going to open i'm going to go open again okay and you can see that you have the quantity extraction which is the query and the sample superstore and one thing i actually recommend if you have tables you know put like a letter q in front of a letter t that way you know which ones are tables and which ones are queries a little helpful tip when you have heaps of them so i can connect directly to this query okay and i can grab it but the problem with this is that if you're going to be connecting directly to queries in whatever system you have you have to build a query first and in my line of work sometimes we don't need a formal table because we're just testing something or just prototyping or we just want to grab the data just for a very specific purpose it's not something we need to do ongoingly ongoingly so it's sort of like the custom sql i found is very useful for things that are on the fly that you need to just adjust as you go along it's not like business as usual reporting for example okay so what i can do is instead of using that query the query one i can just build the sql straight into here so if i go into this query if you don't know how to actually write queries like from scratch um there's heaps of content out there i don't know if people want me to teach them sql i'm not like i can use seo but i'm not a super duper expert i'm more of a tableau guy but i can write sql no problem uh yeah anyways so i can take this sql and i'm just going to copy this because i'm lazy and we can go new custom sql now from memory the custom sql only appears when you connect to databases but i have heard in earlier versions you could do it with any data set but i don't know how true that is or the forums that i was reading was actually just wrong okay so now you have your query here and you know for every query it's usually structured in a certain way so it's like a bit more legible all right like that okay and one of the things when you're doing custom sql in oracle databases or in access or whatever whenever you do a select statement right or a query there's usually always a semicolon at the end tableau doesn't actually like semicolon so you have to get rid of it you can only do one query at a time here so we go okay and there we go so this is a brand new on the fly query and what i recommend is always to rename it so this is going to be your category quantity okay now you can do whatever you want to this you can do further joins you can do further whatever you want you can connect it to all sorts of things right it's completely up to you but that's how you would do your custom sql and by the way this question i should have mentioned this earlier let me bring this over here this question came from dileep right so um the leap hopefully this answers your question if you guys want to follow along with the example i've attached all the working files in the description below so feel free to have a play until next time have a good day and bye

Channel: Jellyman Education

Views: 433

Rating: 5 out of 5

Keywords: When Can I Use Custom Sql In Tableau, How Do I Write Custom Sql In Tableau, Getting Started With Tableau Custom Sql, Tableau Custom Sql Basics, Tableau Custom Sql, How Do I Connect Tableau To Microsoft Access, What Are The Benefits Of Using Custom Sql In Tableau, I Can'T Find The Custom Sql Function In Tableau, Tableau Custom Sql Function

Id: nXN5kuSixmA

Length: 6min 59sec (419 seconds)

Published: Thu Sep 09 2021