Gysbert is correct that you need some sort of relationship between the 2 tables to display data from both of them in the same chart. I would suggest concatenating rather than linking--i.e. try to create a true star schema. This is beneficial both in terms of performance and overall aesthetics (since both tables are fact tables). Try something like this:
Fact:
LOAD
DATE as DateKey,
ITEM,
QTY,
1 as SalesRow
RESIDENT SALES;
CONCANTENATE (Fact) LOAD
YEAR & '|' num(MONTH) as DateKey, //this assumes you have a 4-digit year and numeric/dual month
ITEM,
COSTPRICE
1 as CostRow
RESIDENT COSTPRICES;
DROP TABLES SALES,COSTPRICES;
//Next, create a master calendar that will contain all your sales dates
min_max_dates:
LOAD
min(DateKey) as mindate,
max(DateKey) as maxdate
RESIDENT Fact
WHERE SalesRow=1
;
LET vMinDate = peek('mindate');
LET vMaxDate = peek('maxdate');
LET vNoDays = '$(vMaxDate)' - '$(vMinDate)' + 1;
DROP TABLE min_max_dates;
Calendar:
LOAD
DateKey,
DateKey as Date,
year(DateKey) as Year,
month(DateKey) as Month,
date(monthstart(DateKey),'MMM YYYY') as MonthYear,
'Q' & ceil(month(DateKey)/3) as Quarter
;
LOAD
date('$(vMinDate)' + recno() - 1) as DateKey
AUTOGENERATE $(vNoDays);
//Adding "generic key" rows to master calendar to create matches on Cost data
CONCATENATE (Calendar) LOAD
Year & '|' & num(Month) as DateKey,
Date,
Year,
Month,
MonthYear,
Quarter
RESIDENT Calendar;
Regards,
Vlad
