We all know that the quality of data going into an analysis or report affects the accuracy of the results. Data cleansing is more than correcting spelling errors and data duplication. It also involves summarizing, filtering and extrapolating the data to identify exactly what it is that you wish to analyze and report on. FreeSight presents you with powerful unique and patented functions to simplify these operations for you.
In this brief write up we’ll explore some of FreeSight’s unique features for these key data cleaning topics:
- Editing and Data Management
- Use of Linked Copies
- Use of Reference Tables
I. Editing and Data Management:
Just a few unique features you’ll want to know about:
- When you edit or correct cell values in a Table, a red pencil icon illustrates that you’ve made a change. You can always see what the original value was by hovering your mouse over the icon. And you can always revert back to the original value by selecting Remove Correction(s). If you wish to change all current matching values in the Table to a corrected value, select Correct Matching Values. Triple red pencil icons appear. Remove Corrections command reverses the action.
- As in any spreadsheet, you can move rows and columns around. At any time, Reset Row / Column Order reverts them back to their original order.
- Column Data Type lets you tell FreeSight what type of data you are working with (e.g. text, numbers, dates, etc.), if FreeSight didn’t automatically assume correctly. There are certain functions that apply explicitly to different types of data.
- Features to add or delete rows and columns are as you would expect, except that any original data you delete can be undeleted at a later date.
A bit about working with FreeSight Functions and Formulas:
- When you apply a function or formula to a column, FreeSight automatically applies it to the whole column. You don’t need to copy and paste the formula down the column.
- FreeSight contains just about every type of function or formula that you’d expect to find in a spreadsheet, plus over 60 data manipulation functions and formulas that are unique to FreeSight. Many of these are Text functions that make it very easy to normalize and format strings of data that are in desperate need of cleaning due to inconsistency of data entry, or differences in formatting from multiple sources. Date, Lookup, and Summarization functions provide powerful ways to drill into your data that would take hours to complete and sophisticated queries to perform using other tools.
- The complete list of FreeSight Functions is found here. All of those shown in red are unique to FreeSight.
Filtering and Sorting in FreeSight are also simple and straightforward:
- When you apply a Sort to your table, all of the data in each row always stays together. There is no getting out of sync in a sort! Click on the Sort buttons on the FreeSight Command Bar (on the top of the screen under the Menu Bar.)
- Using Data/Sort from the Menu Bar, you can also tell FreeSight how to treat special conditions during a Sort, such as how to treat numbers as text or numerics, whether or not to ignore capitalization, or how to deal with leading or trailing spaces, and multiple spaces within text values.
- Filters have many standard features, such as the ability to create them using drop-down arrows from column headers or field labels and easily selecting the values you wish to see.
- Use Custom Filters to create a filter by a combination of conditions (equals, does not equal, greater than, less than, etc.)
- You can easily filter by specific date or date groupings (e.g. day, month, year, etc.)
- Use the Filter Find function create a filter based on specific values you search for, or use the Filter Column By Selected Value function to create a filter based on a selected value.
- You can turn a defined filter on and off (Pause or Refresh the filter).
- When a filter is “on” in any Table, FreeSight will let you know by shading that Table with a rose hue. Within the Table itself, the filtered row and column headers are also shaded rose accordingly, and the column header also displays a filter icon.
II. Use of Linked Copies:Among the most powerful features of FreeSight are Linked Copies. Linked Copies of your Tables are what enable you to perform multiple sequential or concurrent operations with your data, and retain an easy to understand audit trail of every step you have taken. Best practices in any multi-step process include documenting each step of that process. With FreeSight, best practices would suggest that each major operation performed with your data should occur in a new Linked Copy of your Data Table. The new Linked Copy should be named in accordance with the steps you’ve taken within. Also, where appropriate, comments can be created for any Table that appear when you hover your mouse over a commented table. In these ways, your FreeSight model is a fully transparent and easy to follow audit trail of your work.
Answers to FAQs about Linked Copies
- You can have as many as you might require in order to complete all of the various operations you wish. These can be on the same stream of a workflow, or you can create them in separate streams. They can diverge and link back together again as you wish.
- Common uses of Linked Copies (to name just a few) include:
- Anytime you are applying data restructuring operations, such as adding additional columns to accommodate parsing (Text To Columns) or creating new derived fields or columns from formulas or other means.
- Anytime you wish to retain work you’ve completed, yet continue working within the same dataset to perform other operations.
- Anytime you wish to Analyze your data (using Analysis Tables), and then continue working with other aspects of the same data set.
- Anytime you wish to divide your data into filtered subsets to perform analyses on each.
- Anytime you wish to summarize data and then relink the summarized data with the original or other summarized data.
III. Use of Reference Tables:FreeSight Reference Tables are so easy to create and use that they entirely replace the need for VLookup types of functions. Reference data are lists of customers, code definitions, sales reps, price lists, region tables, etc. that are joined to data from other sources such as sales or other business transactions or research results. Together they expand the breadth and depth of your analysis. If you already have files of reference data available, just drag and drop them into FreeSight, and FreeSight will join them to your other Data Tables instantly and automatically. But FreeSight also has the built-in intelligence to automatically create Reference Tables for you or easily allow you to create them yourself. Whenever your data contains lists or codes of reference-able data that you might wish to use in additional ways, then FreeSight’s Auto-Create Reference Tables function will sub-select the reference-able data, and break it out into separate summarized tables that you can edit and manage independently as needed. (The Create Reference Tables function allows you to do the same thing manually.)
Once created, FreeSight’s patented tools allow you to correct, edit and maintain Reference Tables simply and easily. If you haven’t already seen our three-minute demonstration video on this topic please do so now. It’s much easier to show than to describe in words:For more videos, click this link.