Documentation Visual SQL FAQs

Add missing column after Pivot in Visual SQL

If you joined before January 6, 2020, you might be using our old Data Explorer interface. Check out Add missing column after Pivot in Data Explorer for more relevant content.

Let’s say you have three 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 Dundersign Demo Data data source. We want to get how many of each document type are created over a specified number of days; that number is pulled from a Text Input Control. We know there are three distinct values in our for the Type column: img, pdf, and word. However, depending on our filter, there may not be data for one or some of those document types for the given time period. In our example, there are no rows related to the “img” document type when we query data over the past day, so when we pivot that column in our Pipeline, there isn’t be a column for “img”!

Missing Print column after Pivot

So what can we do? To make this work, we need to broaden our time range so all the distinct values are available in our initial query. For this example, we get at least one row for each document type value when we look at the last 10 days rather than just one day.

Pivot creates a column for all values

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

Create a new column using a Formula Column using the following Custom formula:
```
 "column_name" = null
```
replacing column_name with the value.
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.

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