I am looking for a way to show items with no data in Superset Pivot tables. In Excel, there is a simple way to do it. I was not however able to find it in Superset. The only way that occurs to me is to create the table by hand, which is not an optimal solution for me. It is even more complicated because it contains dates.
Example:
Consider this table:
date organization status count
2023-10-01 00:00:00 Organization_1 A 3
2023-10-01 00:00:00 Organization_1 B 2
2023-09-01 00:00:00 Organization_1 B 1
2023-08-01 00:00:00 Organization_1 A 1
I would need:
2023-10-01 00:00:00 Organization_1 A 3
2023-10-01 00:00:00 Organization_1 B 2
2023-09-01 00:00:00 Organization_1 A 0
2023-09-01 00:00:00 Organization_1 B 1
2023-08-01 00:00:00 Organization_1 A 1
2023-08-01 00:00:00 Organization_1 B 0
I tried searching here and on GitHub for this functionality. Also tried to create the table by hand, but was not successful.
You can cross join statuses with dates and left join the table :
select s.date, s.organization, s.status, COALESCE(t.count, 0) as count
from (
select j.date, i.organization, i.status
from (select distinct organization, status from mytable) i
cross join (select distinct organization, date from mytable) as j
) as s
left join mytable t on t.organization = s.organization and t.date = s.date and t.status = s.status
order by s.date desc, s.status
Results :
date organization status count
2023-10-01 Organization_1 A 3
2023-10-01 Organization_1 B 2
2023-09-01 Organization_1 A 0
2023-09-01 Organization_1 B 1
2023-08-01 Organization_1 A 1
2023-08-01 Organization_1 B 0