Showing all records from one fact table where no associated data exists in a second fact table

[#1]

Hi Folks,

I have a problem that I'm trying to overcome in a chart and I'm hoping I don't need to create bogus records in the script.

I have an inventory file that contains inventory balances of all items. I am bringing in sales data and summarizing the rows by item and MonthYear with a QVD from a Sales Application. I'm trying to determine the slow moving items using the rolling 12 month sales (for example by selection Jan-2010 through Dec-2010). The resulting chart is based on 12 months selected and looks like this:

Item	Description	On Hand	12 Months Rolling Sales	Excess Inventory 24 Months	Excess Inventory 12 Months
1234	XYZ	52	22	8	22

The problem I've come to realize is that if I have no sales for the selected periods and there is inventory, no row will be presented since the selection will only bring in invenotry records for which there is sales. Can anyone think of a way to force all records from the inventory file even if there is no sales data present for the selected MonthYear? Ideally the resulting chart would contain a row for item 1235 where there is inventory and no associated sales data is present:

Item	Description	On Hand	12 Months Rolling Sales	Excess Inventory 24 Months	Excess Inventory 12 Months
1234	XYZ	22	22	22	22
1235	ABC	10	0	10	0

Thanks,

Rich De Rocco

[#2 · Accepted solution]

Hi Rich,

When bringing in the data, are the Sales and Inventory coming in separately? If so, you could create a LINK table that brings the comon data elements in with an OUTER JOIN.

ie:

Sales:
SQL SELECT
*
,Date & Item_Id & Description AS '%Sales_Key'
FROM SALES;

Inventory:
SQL SELECT
*
,,Date & Item_Id & Description AS '%Inventory_Key'
FROM INVENTORY;

Link_Sales_Inventory:
LOAD
Date
,Item_Id
,Description
,'%Sales_Key'
RESIDENT Sales;

OUTER JOIN (Link_Sales_Inventory)
LOAD
Date
,Item_Id
,Description
,'%Inventory_Key'
RESIDENT Inventory;

DROP FIELDS
Date
,Item_Id
,Description
FROM sales, Inventory;

Thanks,

Aline

[#3]

Thanks AQline,

I was afraid of that. I was hoping to get away with just a chart function.

Rich

Thanks Aline.

Just got around to making the change.

Rich