Tableau Tutorials – Import Text Files




Welcome to another post in the series ‘Tableau Tutorials’. So far we have covered many basic topics, in order to make sure readers get familiar with the interface. Since there is a lot of interaction with this interface in order to prepare the dashboards, gaining familiarity with layouts is important in order to troubleshoot and prepare quality results.

In this post we would look at how the Tableau tool deals with text delimited data. We would show a few cases of the data may be imported incorrectly in order to demonstrate the pitfalls of the interface. Lets start with the importing the file into Tableau. We should a scree as shown in screenshot below:

When working with text files, it is important to remember that delimiters are always in play when importing data into any tool. In the above example, we have considered a file, which is a comma delimited file. Let us open the extra options for importing a text file by clicking on the arrow icon (which would appear when mousing over the filename in the top pane). See screenshot below:

It can be seen that there is one different option in the case of text files from when we discussed ‘importing excel files in tableau‘. Let us see the various options that appear when we click on ‘Text File Properties…‘.

The options that appear here are the same options we get in any tool when we import text data files. Here we can specify the appropriate delimiter and text qualifier for the data. Let us consider a scenario of what may happen, if we change these default values.

In the above screenshot, we have changed the delimiter to ‘Vertical Bar’ and set the text qualifier to ” character. in the bottom pane, we can that the data preview has gone completely bonkers. It is because, firstly, we have considered the incorrect delimiter and to make it worse, we have chosen an invalid text qualifier.

When working with the text files, users should open and study the files carefully to know these details so that they may updated correctly in these options. Please note, users shouldn’t always trust the Tableau software when importing data files, especially delimited text files as it is not always apparent if there an error in the import.

Keep practicing these methods and let us know in case you face any doubts. You can find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Tableau Tutorial – Importing Excel Files




In this post of ‘Tableau Tutorials’ series, we will be discussing importing excel files into the Tableau interface. Each file type has its own quirks. For instance, Excel files have multiple tabs and access files might have multiple tables to choose from. Tableau allows users to leverage these tabs or tables simultaneously. We expect, if we discuss a couple, it should prepare users for the rest. So let’s dive right into it.

Let’s start with the same steps as we discussed previously to connect with the data files. We should reach to this screen once we have connected with an excel file.

When an excel file is imported, we can see the various tabs in the file listed in the left pane below the connections. We can select the relevant sheet and drag it to the top pane, where we can read ‘Drag Sheets here’. Once we drag the required sheet into this area, we should see a more familiar screenshot.

Also, users may want to join data from different tabs together. In such cases multiple sheets can simply be brought together and Tableau would suggest the appropriate fields to join the data files. We will discuss joins in tableau in a later post.

There are some options that are seen as part of importing excel sheets. If we click on a small arrow that would appear on the sheet name in the top pane. See screenshot below:

These options allow users to select options based on the data file. For instance, in case the field names are not present in the first row of the data files, it would be important to undo this selection so that we may capture the data correctly. Similarly there are other options in this window that can assist with the data import depending on requirements. As you become more experienced, some these options make start to seem useful.

We encourage a lot playing around with this interface. It is important to get familiar with the interface in order to productive quickly.

You can find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Tableau Tutorial – Data types in Tableau




Every analytics tool has it’s own way define the data types in accepts. Its not to say that these conceptually different, but they are modified to suit the tasks performed by the tool. In this post of ‘Tableau Tutorials’ series, we will discuss the data types in Tableau. So let’s take a close look at the data types used in the Tableau. Just like, MS Excel, Audit Command Language, SAS etc, data types are divided up into four categories. These are string, numbers, dates & geographical locations. The difference lies in how these are used in Tableau. For instance, dates are not required to be formatted to be imported into Tableau. A character field in a text file, which has a date format is read as date regardless of the format i.e. the dd/mm/yyyy. This is true for other fields as well.

Essentially, since tableau is not designed to perform heavy data transformations, it is not as picky about the data formats of the fields in data files. The end goal is to be able to perform new calculations and create graphs and charts. The design philosophy indicates that Tableau has taken away the features of data formatting from MS EXCEL and focused that computing power to perform real time calculations and creation of visualizations. Let us quickly review the data types in Tableau:

  1. Strings – All character values such names, IDs, description fields etc. are tagged under this data type.
  2. Numbers – Any fields, which can used to provide quantitative values to visualizations are considered numbers. Fields such as sales, profit values, discounts etc. can all be assumed as numbers.
  3. Dates – Any date values in a data set should ideally be classified as dates type because Tableau provides specific functions and visualizations to deal with date values.
  4. Geographical locations: This is a data type unique to Tableau. Essentially, Tableau has the capability to extract the geo-location of cities listed within the data files. This allows users to create the iconic visualizations using world maps.

Consider the screenshot below to understand, where in the interface can users make changes to data types information:

This screenshot shows the top rows of the bottom pane. At the top of this pane, the different types are listed along with the field. The column for profit is listed as ‘number’, ship date is listed as ‘date’, postal code is listed as ‘geographical’ and region is listed as ‘String’. If we click on the data type indicator i.e. for example the globe symbol on column ‘city’, it gives us options for different data types. See screenshot below;

In the screenshot, we are trying to change the data type for ‘Region’ from string to ‘Number’. This is clearly problematic. See screenshot for result of this change:

When we change the data type for ‘Region’ to number from string, it gives us null values. This is expected as it makes no sense to have the number type for this field. This is not true for every case. There are fields that can be mapped as multiple data types. However, Tableau itself selects the best option.

You can find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Tableau Tutorial – Importing Data Files




In the previous post, we discussed the steps to install and set the Tableau Public software. In this post we will discuss the steps to import data files into the tool. Tableau supports a huge variety of data formats ranging from text files to advanced database formats such as DB2. However, in the public version there is a limitation on these formats. Nevertheless, we should be able to simple files like Excel, Access or text to discuss the various features of the tool so that users can understand the data transformation steps, which are applicable to any data type.

To start importing any data files, first open the tool and you should see this screen

On this screen click the relevant file type you wish to import. To start off, we will import and text (.csv) file. Simply click the ‘Text File’ link as shown in the screenshot above. It should open a file open dialog box. Simply choose the file you choose. It should appear as shown in the screenshot below:

The above screenshot is what can be seen typically, when a data file is imported into Tableau. Let us elaborate on a couple of points in the interface shown in the screenshot. Firstly, pay attention to the ‘white space’ on the top right of the layout. This indicates the files that are being displayed in the pane directly below. This space shows the various data sources that are connected in the current workbook. You can add another data source by clicking the small arrow next to the ‘container‘ icon in this space. We will discuss this in more detail in a later post.

Secondly, notice the left pane. It shows the current connection i.e. is the file being used to display the bottom pane. Please note the additional files listed here. These are not previously made connections. These files that are placed in the same folder as the file imported into the workbook.

And finally, we would like you to bring your attention to the pane at the bottom. This pane is giving us a screenshot of the data in the file we have just imported. It is important to remember that tableau is fully capable for making changes to the data in order to correct for data errors in the file. In most cases, files used for visualization would be processed so that there are no errors. Also, in this pane, we are able to the data types of the various fields in the source data. In this pane, users can change the data types as required for the further transformations and visualizations.

In the next couple of posts, we would separately discuss importing text and excel files. Also, we would dedicate a single post to data types encountered in Tableau.

You can find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Tableau – Installing Tableau Public




Welcome back to the series “Tableau Tutorials”. In this post we would be discussing how you might install tableau on your computer so that you can practice the topics we cover in this series of posts. Tableau is an expensive software tool, which goes from 35 USD per month for personal edition. This should indicate the demand this brand commands.

One way to obtain the software is to work with your current employer and get necessary approvals for the tool. The other way would be to download the student edition, this is a one year subscription provided you provide valid student credentials on the Tableau website. The most easy way for the public to obtain the tool is to download the public version.

Open Tableau Public link in your browser. Provide your email address and download the setup file. Note, tableau public is available for both windows and mac platforms. The tableau public page should look something like this

Once you open the software, you should be able to see a screen something like

This screen may vary slightly if you download an older version (in case you already have Tableau installed on your system). This is the latest at this time. Please pay attention to a couple of elements in the interface as shown in the screenshot. Firstly, the Discover section provides some links to tutorial videos on the Tableau website itself. These can be helpful to gain a better understanding about any concept that you find difficult to understand. The other options is the link, which says, Open from Tableau Public. This is noteworthy because, the public version of the tools only allows users to save there work on Tableau Public Servers. This implies that these area available for the rest of the world and anyone can access anyone’s work for inspiration.

Once you have the software downloaded. It’s time to start looking at working with data files.

You can find relevant reference books in the sidebar of this post, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Tableau – Introduction




Welcome to the first post in the ‘Tableau Tutorial’ series. We have been discussing the various aspects of the data transformation capabilities of tool such as BASE SAS and Audit Command Language on this blog up to this point. Now that we have made significant progress on those fronts, it feel like the right time to include a data visualization tool in the mix.

Data visualization is a key component of a data analytics solutions. Data visualization helps businesses make sense of the data points in order to make informed decisions that would affect their bottom lines or identify areas of improvements in their operations and so on. Most business owners are not very adept in understanding numbers so visualizations help them show a comprehensive picture of their business owners. Consider a few sample charts showing salary distribution across the united states.

Salary distribution across the United States of America

 

Number of jobs across states:

The above visualization may be modified to display the sales distributed across the country or discounts or types of products etc. The list can go on and on.

This series of posts is going to focus on the following points:

  1. How to get Tableau software
  2. Familiarity with the the Tableau interface.
  3. Data import into Tableau Public
  4. Data manipulation
  5. Data sorting and filters
  6. Creating graphs and charts
  7. Creating dashboards

These topics should be enough to get beginners started so that they may start feeling confident in applying for jobs in this domain or maybe even volunteer in your current roles. Tableau is a powerful expression of the the most advanced features available in MS Excel. It is a highly valuable skill in the marketplace and we encourage all youngsters to be certified in order to gain an edge in the job market.

You can find relevant reference books in the sidebar, if you should want to purchase the same for further studying. Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




BASE SAS Programming – Understanding SAS Formats




In the previous post we discussed the use of  SAS Informats. It is a useful method to read in the data files appropriately from a variety of formatted sources of raw data files. However, when opting for this method, we end up looking at the raw data in the tables as raw values. For instance, date fields read in by this method are displayed as numeric values and it is somewhat troublesome to understand the actual values as present originally in the raw data. This is where the  SAS formats come in. SAS formats enable us to format the imported data in a way where it would seem presentable and more easily readable.

Let us consider the SAS program as example:

data test;
input  @1 Name $5. @7 Balance 4.  @12 DOB mmddyy10.   @23 Gender $1.   ;
format DOB date9. Balance 4.2;

datalines;
Jimmy 45.5 12/11/1999 M
Julie 23.2 1/14/2011 F
Tom 20 1/1/2001 M
;
run;

In the above SAS program, we are importing the date field ‘DOB’ using  SAS Informats. This field is now being read in as numeric information with respect to the SAS system date. When we apply the SAS format statement in the data step, we can change the way the numeric values are displayed. Check out the output table from this SAS program below:

SAS Program:

SAS Log:

Output Tab:

As it can be seen in the screenshot of the output tab, we are able to display the date values in a format, which is different from the SAS informat that was used to import from the raw data lines. See below the screenshot, where the dates have been formatted using the  SAS format mmddyy8.

Just SAS provides the flexibility to import raw data using a variety of SAS Informats, there are many options to format the data points as well. As you keep progressing further you will get familiar with the most commonly used  SAS Informats & Formats. It is important to practice on actual data sets to get familiar with these concepts as soon as possible. There are a significant number of questions in the BASE SAS certification examination from this topic.

You can find relevant reference books in the sidebar of this post, if you should want to purchase the same for further studying. BASE SAS Certification guide and the Little SAS book are extremely useful for preparation and also act as a wonderful reference for SAS concepts.

Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Categories SAS

BASE SAS Programming – Understanding SAS Informats




Up to this point, we have discussed the various scenarios to understand how to input data into SAS. In this post of the “BASE SAS Tutorial” series, we would take a more detailed look into how SAS implements the use of the Informats with the Input statement.

We would of course refer you to a book like ‘SAS by Example‘ by Ron Cody to get the formal understanding.  However, we will aim to provide a more workable understanding. Let us consider the datalines as listed below:


In these data lines, one way to read in all the fields is to simply import all of the fields as character values. However, informats allow us to store the values as they are intended. For instance, if we store the date values in these data lines as a character data type, it would be unavailable to leverage any date specific calculations that we may wish to perform. We would end up converting it to a numeric/date format and then applying the desired functions.

Let us consider the outputs from both approaches i.e., importing the date field as a character and using informats.

SAS Program/Output when Imported as character:

data test2;
input  @1 Name $5. @7 Balance 4.  @12 DOB $10.   @23 Gender $1.   ;

datalines;
Jimmy 45.5 12/11/1999 M
Julie 23.2 1/14/2011  F
Tom   20    1/1/2001  M
;
run;

Output/Log:

SAS Program/Output when Imported with Informats:

data test1;
input  @1 Name $5. @7 Balance 4.  @12 mmddyy10.  @23 Gender $1.   ;

datalines;
Jimmy 45.5 12/11/1999 M
Julie 23.2 1/14/2011  F
Tom   20    1/1/2001  M
;
run;

As it can be seen in the above outputs from the two SAS programs, the SAS informats enable allow the storage of the values based on the formats present in the raw formats. However, it is worth noting that although SAS informats  allow for data points to be stored correctly, it is rather inconvenient to view them as the numeric values. It is not always clear as to what the actual date values are. These are addressed by the SAS format statements. These will be discussed in later posts. Keep in mind, SAS Informats  are available for many standard layouts for dates and numeric values (to address decimals, currencies etc.). For instance, amounts may have currency formats or the comma formats depending the region where the data is extracted. SAS Informats are very useful to capture such layouts accurately.

We certainly hope, this was useful in clarifying the understanding. Some of the most tricky questions in the BASE SAS certification are picked from this topic. We recommend, please keep practicing and working hard.

You can find relevant reference books in the sidebar of this post, if you should want to purchase the same for further studying. BASE SAS Certification guide and the Little SAS book are extremely useful for preparation and also act as a wonderful reference for SAS concepts.

Please sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.




Categories SAS