Documentation Visual SQL (beta) FAQs

Add missing column after Pivot in Visual SQL

Let’s say you have five distinct values in a column, but your result set doesn’t have a row for one of those values. When you use a Pivot Action on that column, the missing value won’t become a new column in your transformed result set. If you want this value to still be present, even though there are no rows for that value in your current result set, here’s how you can add that missing column.

For our example, we’ll use our SaaS Company Demo Data data source. We want to get the total cost of each marketing campaign over a specified number of days; that number is pulled from a Text Input Control. We know there are five distinct values in our for the Description column: Adwords, Event, Print, Sales, and Web. However, depending on our filter, there may not be data for one or some of those campaigns for the given time period. In our example, there are no rows related to the Print campaign when we query data over the past 12 days, so when we pivot that column in our Pipeline, there isn’t be a column for Print!

Missing Print column after Pivot

So what can we do? After the pivot, we’ll do the following for each distinct value we know is in our column:

  1. Create a new column using a Calculated Column using the following Custom formula:

     "column_name" = null

    replacing column_name with the value.

  2. Rename the new column so it has the same name as the original.

Here’s how it looks when we do this for our example:

If the column name already exists, Chartio appends :1 to the name of the new column with the same name to distinguish between the new and the original. This is why we use the same column names for our new columns—so we can identify if the column existed already or not. Now we can hide any columns with :1 appended to it, which would be all of them except the Print column.

Hide duplicate columns marked with :1

Now all of our campaigns are visible in the final Table chart!