Postgresql Basics

I’m back to tell you a few basic things about Postgresql for anyone struggling or just looking for answers in general.

The main basics still apply!  SELECT, FROM, JOINs, WHERE, HAVING, GROUP BY, ORDER BY.

SUBQUERIES:  All subqueries need an alias.

COALESCE:  Used like ‘NVL’ or ‘ISNULL’.  Here’s the syntax:  COALESCE(<column name>, <value to be used if it is null>, <next value to be used if that value is null>).  Here’s an example:  COALESCE(Invoice_Amount, 0).  So this example means in the event the ‘Invoice_Amount’ column is null, you would like a zero placed there.  You can also use cascading COALESCE values.  Here’s the syntax:  COALESCE(<column name>, COALESCE(<value to be used if it is null>, <next value to be used if that value is null>)).  Here’s an example:  COALESCE(Trip_Purpose, COALESCE(Exempt_Reason, ‘None’)).  Note:  Don’t enclose the field names in single quotes; only do this with a value you are identifying like ‘None’ in this example.

REPLACE:  Super helpful.  Allows you to replace something in a field with something else.  When to use?  Well, if you’re going to export the query to a csv file and some of your fields have commas, you’ll want to use this handy feature.  Here’s the syntax:  REPLACE(<column name>, ‘<value in field to replace>’, ‘<value to put in its place>’).  Here’s an example:  REPLACE(Job_Title, ‘,’, ‘ ‘).  May be hard to see but in this example we want to replace the comma with a space.  The value to be replaced and the value to replace it with need to be enclosed in single quotes.  An example of a before and after:  Value before REPLACE:  Manager, Operations.  Value after REPLACE:  Manager Operations.

Well that’s all I have time for today.  Stay tuned for more helpful info on various topics including Postgresql, Business Objects, Crystal Reports, Cognos, and Tableau!

BITietje 🙂

 

Tableau connected Medicare enrollment dashboard

(This post was originally published on BIHappy Blog)

Our medicare enrollment database continues to grow and now contains over 9M enrollment records across the country. I began collecting this information almost two years ago with my colleague Josh Tapley, and we used it to produce our medicare advantage dashboards using the SAP Dashboards (Xcelsius) tool, as well as our HTML5 reporting solution. Aside from being an interesting dataset, relevant to medical insurance professionals and anyone else interested in medicare and healthcare, this platform provides us the medium to demonstrate many technical advantages and techniques we often solve on projects. So, to add to our arsenal or medicare advantage dashboards, I have now added a Tableau version. This version looks and operates just like it’s siblings from SAP and our custom HTML solution, however uses completely different technology under the covers. To create it, we had to overcome several interesting challenges, from the ability to serve up Tableau content from our secure server which resides behind our firewall via secure proxy to the internet, addressing proxying, authentication and security challeneges to the ability to create visuals which do not exist natively in the tool, such as a donut chart. This dashboard is connected to the live data, and executed a query each time a state is selected. This design pattern is consistent across all three versions of this dashboard and is designed to demonstrate the ability to work with these dashboarding tools in a completely “hands free” no hassle, and no maintenance mode, where data is refreshed in the database and automatically reflected in the dashboard with no need for any intervention. Enjoy.