by ·0 Comments
Table Joins

Understanding Table Joins in SQL

Working with data often involves the need to utilize multiple data sources, usually stored in different data tables (in case of database storage) or data frames (when it comes to programming languages or data visualization tools.)  In order to put power of this data to a good use we want to be able to join these tables based on a field or fields they have in common (foreign key[s]) or sometimes values in the field that are different. Not only basic principles of table joins – INNER, OUTER (FULL, LEFT, and RIGHT), CROSS (or Cartesian) or even UNION-ing tables are universal to most relational databases and flavors of SQL, they also apply to working with data frames. In this post we will explore examples of using these table joins in a PostgreSQL database, while adding SELF, and LEFT/RIGHT exclusive joins for a good measure.

Continue reading

by ·6 Comments
XLOOKUP Function

I personally don’t know why it took this long for Microsoft Excel team to create XLOOKUP function. The fact that VLOOKUP is considered to be one of Excel’s most widely used functions reflects a strong demand in string look up tabulations. Surely, a multitude of VLOOKUP‘s limitations can be overcome with patience, helper columns, INDEX/MATCH, CHOOSE, OFFSET and other constructs. Yet, why would we use any workarounds, when we would rather utilize a more powerful function with multitude of applications? Meet, much anticipated XLOOKUP function, which was officially released to Office 365 subscribers in early February of this year. It offers a really long list of additional benefits; in today’s tutorial we will review 11 scenarios that take full advantage of the following XLOOKUP features:

  • LEFT lookup
  • Horizontal lookup
  • Multi-cell/array retrieval
  • Match based on wildcard conditions
  • Combination of Vertical AND Horizontal lookups
  • Lookup based on multiple criteria
  • Lookup in reverse order
  • Lookup for maximum/minimum values
  • Built-in Error Handling
  • Exact match by default
  • Flexible approximate match

Continue reading

by ·0 Comments

First Impressions of using Qlik Sense cloud, using Survey of Business Owners data


First Impressions of using Qlik Sense cloud, using Survey of Business Owners data.

          There is certainly no shortage of various data visualization and BI tools on the market. On this blog we’ve already covered Tableau and Power BI , it’s time for us to review their competition, another leader on Gartner’s Magic Quadrant for Analytics and BI platforms: Qlik , which offers a suite of different BI tools. For the purposes of this post let’s focus on their web-based free product: Qlik Sense – Cloud. Most of the modern data viz programs are supposed to be rather intuitive and very easy to use; so I decided to play with this program without going through the trouble of learning to use it first. American data finder had just the right data set for this experiment: Survey of Business Owners data, which among other things can help us quantify number of companies by size and owner’s gender, see if male vs. female-owned organizations earn higher revenue, employ more workers, and/or pay higher salaries to their employees. Let the data discovery journey begin.

Continue reading

by ·0 Comments

Getting started with Microsoft Power BI using Google Merchandise store data.


Getting started with Microsoft Power BI using Google Merchandise store data

          While I’m as loyal to Excel as the next analyst, when it comes to data visualization and interactive dashboards, Tableau is my tool of choice. If I need to analyze large data sets, I prefer to get cozy with the data by writing SQL queries in whichever database environment such data might be stored. In the meantime, the world does not stand still, and Microsoft has been making substantial progress with a product offering they called Power BI . In fact, this tool offers data preparation, data discovery, dashboarding and custom visualization features starting with a free version for up to 1 GB of stored files and a modest $10 monthly plan for the beefed up PRO version. It’s definitely long overdue, but I finally got around to playing with both: Power BI desktop and cloud-based versions, all while using publicly available data from nonetheless, but Google’s merchandise store. , available through their demo Google Analytics account. Which other etailer can boast growing their Cyber Monday sales by 274% to $54K, while keeping their marketing advertising budget under $ 100?

Continue reading