Populate missing information

[Anonymous]

I have reports for roughly 125 offices.

In my qlikview report I have one table with information about all 125 offices. This information is static and rarely changes.

I have a second Fact table that comes from weekly reports I receive.

The issue I am having is that the reports come only with the offices that have data to share. I need to populate a zero for the offices not listed.

My office list are driven by office number. It is not a straight 1 through 125 but there are 125 numbers that each refer to the office.

The report I get is two columns: Office number and the another number field but this report only has lines for the offices that have a number to report. If it is Zero then it doesn't appear on the report. Is there a way to populate a Zero for these missing offices so an average would be a true average rather than an average of when they report a number?

Thanks in advance for the help,

[swuehl]

Have a look at

Generating Missing Data In QlikView

Another option would be to not use Avg() function but a division of your aggregated numbers by the selected number of weeks (or any other number that would return you the corret denominator)

=Sum( MeasureField) / Count(TOTAL DISTINCT Week)

[johnw]

Perhaps like this?

[This Week's Reports]:
LOAD
[Office]
,[Office] as [Reporting Office]
,[Number]
FROM somewhere
;
CONCATENATE [This Week's Reports]
LOAD
[Office]
,0 as [Number]
RESIDENT [Offices]
WHERE not exists([Reporting Office],[Office])
;

DROP FIELD [Reporting Office];