Creation of Matrix Based on 'Empty" Fields

Q&A
,
1

Hi there,

I’m trying to build a matrix visual (pivot table) in Amazon QuickSight with the following layout:

Rows: site, site_level_1
Columns: sector
Values: distinct count of checklist_nid

The goal is to ensure that each site has visibility into which sectors are covered.

However, there’s a challenge:
Some site × sector combinations are not applicable, and in those cases, no data exists at all—not even a NULL or 0. As a result, those cells are completely missing from the pivot table.

This causes a problem when trying to apply conditional formatting to visually “blank out” or highlight non-applicable intersections. Since the cells don’t exist, formatting rules can’t be applied.

We’ve tried using calculated fields to convert NULL to 0, but that doesn’t help because the rows themselves are missing—not just the values.

What’s the best way to ensure all site × sector combinations appear in the matrix, so we can apply formatting—even if the value is zero or not applicable?

2

Hi @Roger123 ,
I think that a solution here will include doing a little bit more work preparation side.

The problem is that also you need to consider all the combinations of site x sector x site_level, I don’t think there a quicker way to solve it.

Let’s see if this works out.

Supposing you have you data in a DB, you can e.g. getting all possible combinations of site X sector x site_level (no matter if existing at the moment or not) by doing a query like:

WITH 
sites AS (
  SELECT DISTINCT site FROM "yourDB"."yourTable"
),
sectors AS (
  SELECT DISTINCT sector FROM "yourDB"."yourTable"
),
levels AS (
  SELECT DISTINCT site_level_1 FROM "yourDB"."yourTable"
)

SELECT
  s.site,
  se.sector,
  l.site_level_1
FROM sites s
CROSS JOIN sectors se
CROSS JOIN levels l
ORDER BY s.site, se.sector, l.site_level_1;

Then have ready another query to get all your data:

SELECT * FROM "yourDB"."yourTable"

First create a dataset with siteXsectorXLevel query, then click on Add Data button (top right in Data Prep view) and add another dataset from the same source with all the data (RealData).

Now you can apply a Left Join on Site, Sector and Level:

image
image2174×692 54.2 KB

After doing so, you should have some rows also for the combinations which have no data, so with null (you can hide duplicated fields site and sector and level, the ones coming from the right side of the join). So if you go and create a table, you’ll see these null values:

image
image1054×380 40.9 KB

You see here that you have all combinations possible for the three fields, and then null on checklist_nid where there is no data.

Now, doing a pivot, you should have to possibility to have 0s for empty combinations.

image
image1381×704 59.1 KB

Hope this helps! Let us know :slight_smile:

Andrea

3

Hi @Roger123

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hi @Roger123

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!