How to Join with 'filling in' of missing values

[MikeH1983]

Hi everyone, hoping someone can help me with this one.

I am trying to join two tables of people, month end dates and facts, where the fact table does not have a complete date set. I want to fill in 0 for dates where there is no entry on the fact table.

I normally join tables with a Left Join, and then load a result table where the dates are equal:

Left Join (Table1) 
 Load *
Resident Table2;

ResultTable:

NOCONCATENATE Load *

Resident Table1

Where FactMonthEnd = MonthEnd

However I cannot figure out how to insert the zeros. This script excludes the dates for which there is no Fact value, but I need a full date set since I am adding other data.

Here is my desired result below. Can anyone help me? If the solution is to expand the FactTable and insert the zeros there, can you suggest a way I can do that instead? Thanks very much.

Table1:			Table2:				ResultTable:
Person	MonthEnd		Person	FactMonthEnd	Fact		Person	MonthEnd	Fact
1	2018-01-31		1	2018-01-31	10		1	2018-01-31	10
1	2018-02-28		1	2018-03-31	10		1	2018-02-28	0
1	2018-03-31		1	2018-05-31	40		1	2018-03-31	10
1	2018-04-30		2	2018-02-28	20		1	2018-04-30	0
1	2018-05-31		2	2018-04-30	20		1	2018-05-31	40
1	2018-06-30		2	2018-06-30	10		1	2018-06-30	0
2	2018-01-31						2	2018-01-31	0
2	2018-02-28						2	2018-02-28	20
2	2018-03-31						2	2018-03-31	0
2	2018-04-30						2	2018-04-30	20
2	2018-05-31						2	2018-05-31	0
2	2018-06-30						2	2018-06-30	10

[Gabriel]

Hi,

I have similar issue in one of my application.

My solution was,

1. Find and createnfull date MinDate to MaxDate in your script. Find the min & max from the main table.
2. Create another table with the field  you wanted to populate with zeros (0)
3. OUTRT JOIN the full date table with the new table in step 2 above
4. Now OUTER JOIN the new table which contain the full date with zero table created in step 3 to the main table you started with.

See my blog post for the solution

Hope it helps

[MikeH1983 · Accepted solution]

I took a different approach to solve this problem. I cartesian joined all the FactMonthEnds and Facts to all of the MonthEnds, and took a Sum in a new table that picked up where these were the same date (and would thus generate 0 when there was no aligned date). I have used this same technique before to aggregate monthly absence data in the script. My method:

I copied Table1 to create Table1B.

I joined the Table2 Fact table to Table1, with no common field (FactMonthEnd is a different field name), so a cartesian join.

I created a table MonthValues:

NOCONCATENATE Load Distinct

Person,

MonthEnd as SumMonthEnd,

Sum(if(FactMonthEnd =MonthValueDate,Fact)) as FactValue

Resident Table1
Group By [Person],MonthEnd;

Then I cartesian joined this back to Table1B

Left Join (Table1B) 
 Load
 [Person],
    FactValue,
    SumMonthEnd 
    Resident MonthValues;

Then I filtered this down in a FinalTable where SumMonthEnd = MonthEnd

FinalTable:

  NOCONCATENATE Load
    [Person],
    MonthEnd,
    FactValue    
    Resident Table1B
    Where SumMonthEnd = MonthEnd or isnull(SumMonthEnd);