Though this type of task can be done in the physical layer of the canvas in Tableau by using options like pivot, your database might not support it. In some cases, you might be working with a table that needs to be restructured before analysis. You can use the following custom SQL query to retrieve the specified columns and records thereby reducing the size of the data set that you connect to from Tableau.ĬOUNT(.) AS
When working with very large data sets, sometimes you can save time while working with your data if you reduce its size first.įor example, suppose you have a large table called FischerIris. The result of this query shows the original Root field and the Root field cast as a string.įor more information about joins and cross-database joins, see Join Your Data. You can use the following custom SQL query to change the data type of Root from a number to a string so that you can join the Main and Sub tables using the Root and ID fields.ĬAST(. The Root field is a number type and the ID field is a string type. In cases when the data type of the fields are not the same, you can use custom SQL to change the data type (cast) the field before performing the join.įor example, suppose you want to join two tables, Main and Sub, using the Root and ID fields, respectively. When you want to perform a join between two tables in the physical layer of the canvas, the data type of the fields you join on must be the same. The result of the query looks like this in the data grid:įor more information about the union option, see Union Your Data.Ĭhange the data type of a field to do a cross-database join
SELECT * FROM November UNION ALL SELECT * FROM December You can use the following custom SQL query to append the second table, December, to the first table, November: In some cases your database does not support this option, so you can use custom SQL instead.įor example, suppose you have the following two tables: November and December. If you need to append data to each other, you can use the union option in the physical layer of the canvas in Tableau.
Only relevant fieldsįrom the custom SQL query display in the data grid on the Data Source page.įor more information about the logical and physical layers of the canvas, see The Tableau Data Model. When you click OK, the query runs and the custom SQL query table appears in the logical layer of the canvas. The query must be a single SELECT* statement. Type or paste the query into the text box. Connect to a custom SQL queryĪfter connecting to your data, double-click the New Custom SQL option on the Data Source page. See the Legacy Connection Alternatives document in Tableau Community for alternatives to using the legacy connection. NOTE: Beginning with Tableau 2020.2, legacy Excel and Text connections are no longer supported. To connect to Excel or text files using the legacy connection, connect to the file, and in the Open dialog box, click the Open drop-down menu, and then select Open with Legacy Connection.
Psequel query writer windows#
Though there are several common reasons why you might use custom SQL, you can use custom SQL to union your data across tables, recast fields to perform cross-database joins, restructure or reduce the size of your data for analysis, etc.įor Excel and text file data sources, this option is available only in workbooks that were created before Tableau Desktop 8.2 or when using Tableau Desktop on Windows with the legacy connection.
Psequel query writer how to#
However, using custom SQL can be useful when you know exactly the information you need and understand how to write SQL queries. Because databases have slightly different SQL syntax from each other, the custom SQL you use to connect to one database might be different from the custom SQL you might use to connect to another. To a specific query rather than the entire data set.
0 kommentar(er)
