Archive

Posts Tagged ‘sql’

SQL create a delimited string from a flat file

February 24th, 2015 No comments

I’ve recently written a post on how to split a delimited string in SQL, now I need to do the reverse and take the flat table and create a delimited string.

Fortunately creating a delimited string is far simpler than splitting a delimited string.

Assuming we have a flat table called CountryUsers with the following structure:

Country User
UK Bob
UK Fred
UK Bill
USA Mike
USA Lou
USA Carol
USA Sam

To convert this table into a 2 record table, one for each country, with a comma delimited string of users, run the following SQL: Read more…

Categories: SQL Tags:

SQL split string by a delimiter to create new rows

February 4th, 2015 No comments

I’ve just spent a reasonable amount of time digging through the internet to work out how to take a dataset containing a delimited string and then split the string to create new records. For example you could have a table called Users:

Country User
UK Bob|Fred|Bill
USA Mike|Lou|Carol|Sam

What you want to do is split the string by the delimiter and create new rows:

Country User
UK Bob
UK Fred
UK Bill
USA Mike
USA Lou
USA Carol
USA Sam

Apparently there are many different ways to create this but the neatest I came across was to use a combination of Common Table Expression (CTE), Table Function and Cross Apply. Read more…

Categories: SQL Tags: ,

Tableau Extracts are Slow to Refresh on the Server

July 19th, 2012 1 comment

On many occasions I have found Tableau data extracts are slow to refresh on the Tableau Server when pulling from SQL (and I’m sure this happens with all data connections). I always used to put this down to the slow execution of the query, which could definitely be a cause as if it’s slow to run on the SQL Server it will be slow to run when refreshing an extract. It also appears that Tableau can be the cause of the slow updating in some cirucmstances. I have just come across this write up on extract performance by one of the Tableau staff which they put on their forums. I think it is very useful so I’ve copied it into this article.

Basically it advises what to check to see what the cause is of the slow updating of extracts and advises what to do if the blame is on the Tableau side. Read more…

Custom SQL data connections in Tableau

July 12th, 2012 6 comments

Recently I have had to reshape text files using custom SQL in the data connection. Being reasonably competent in SQL I thought this would be easy. Unfortunately I was wrong. Tableau uses the MS Jet data engine which has slightly different syntax to standard SQL used in SQL server. For example when using the DATEADD function the syntax differs. With DATEADD the syntax also differs between the Custom SQL connection and the Tableau calculated fields. This link could also help with the datepart intervals.

Also the CASE statement isn’t recognised by MS Jet SQL, instead an alternative has to be used, such as nested IIF statements. This post I found suggesting CASE alternatives, written back in 2006, suggests CHOOSE can be used instead, it all depends on how the CASE statement was going to be used. Read more…

Categories: Tableau Tags: , , , ,

Excel Populate a Combo Box from a Database

April 18th, 2012 5 comments

To populate a combo box on a userform in Excel you need to use VBA. Firstly add a combo box to a userform in the VBA window (Alt-F11) of your spreadsheet.

The VBA code below shows how to populate a 2 column combo box. Add this code to the Userform Initialize event to populate the combo box when the form is loaded.

You might need to add a Reference to make the ADODB part of this work. Go to Tools – References in the VBA window and find the highest number Microsoft ActiveX Data Objects 2.X Library. Select the highest 2.X library.


Read more…

Categories: Excel, VBA Tags: , , , ,

Quickest way to remove the time from a datetime in SQL

November 10th, 2011 No comments

In an earlier post I showed some ways to remove the time portion from a date, but the obvious follow on from that is which is fastest. To test I ran both versions on a dataset of 1.9m rows of data.

The DATE datatype returned the records in 15m 35s = 935s

Getting the data using the DATEADD and DATEDIFF technique took 16m 50s = 1020s

In this example using the DATE datatype was 10% faster – which is significant. From this test the best way to remove the time from a datetime in SQL is by using the DATE datatype.

I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimised way by the SQL engine and will be far slower than the 2 techniques I have documented.

Categories: SQL Tags: ,

Tableau Connecting to Data

September 5th, 2011 No comments

Tableau is very flexible when it comes to connecting to data. Using the full paid for product it’s possible to connect to any data source accessible by an ODBC connection, cubes (both SSAS and Orcale Essbase), text files, spreadsheets and a number of different databases.

Tableau Data Connection Options

It’s possible to connect to the live data source directly or to import the data into Tableau as an extract. Importing the data opens up the full array of Tableau inbuilt functionality; for example the quick table calculations, the Count Distinct (COUNTD) function and a variety of others; where these options are often not available depending on the data source. Read more…

Remove a line feed / carriage return from a string

August 23rd, 2011 3 comments

If you ever have to deal with long strings of freetext it’s likely you’ve come across random line feed and carriage returns in that text causing you headaches when it comes to manipulating the text.

Once you know how it’s quite a simple problem to deal with. This article describes how to remove the carriage returns and line feeds in SQL, Excel and Access. Read more…

Categories: Access, Excel, SQL Tags: , ,

Omniture visits report in Tableau – part 1

To recreate the Omniture visits report in Tableau was quite difficult so I’ve broken the post into 2 parts. The result will be published on Tableau Public and I’ll supply the link to the report at the end of the post.

I spent some time recently trying to replicate web traffic visit data from Google Analytics in the style of the Omniture visits report. I like the Omniture visits report as it shows how you’re tracking in the current month vs how you were tracking exactly 4 weeks earlier – i.e. compares Monday with Monday, Friday with Friday, etc. It also shows how you were performing vs last year. Read more…

Tableau DateAdd in Custom SQL Data Connection

In Tableau calculated fields to add weeks to a date the DATEADD function is used. Confusingly the syntax to write a DATEADD in Tableau calculated fields differs the the DATEADD in Custom SQL.

The syntax is DATEADD(‘week’,NumWeeksToAdd,DateToAdd). For example in a calculated field DATEADD(‘week’,4,#1 Jan 2011#) = #29  Jan 2011#.

I tried to use this syntax in the CustomSQL area of a data connection and it threw an error. This confused me for a while until I realised that when it comes to CustomSQL the syntax of the statement needs to be SQL syntax and not Tableau. SQL doesn’t recognise ‘Week’ in the DATEADD function. In SQL the week is represented by ‘ww’ or ‘wk’.

To make DATEADD work in the CustomSQL connection the syntax needs to be DATEADD(‘ww’,NumWeeksToAdd,DateToAdd). The full dateadd SQL syntax is available on the MS website at this link: http://msdn.microsoft.com/en-us/library/ms174420.aspx

NOTE: The SQL syntax won’t work in a  Tableau calculated field – Tableau syntax must be used. Click here for Tableau date syntax.

Custom SQL in Tableau is different to many other forms of SQL, some of which is described in this article explaining some of the quirks of Tableau Custom SQL.

Categories: Tableau Tags: , , ,