Please see the example :
I want to join "BUDGET" and "BUDGETC" in table "BUDGET"
"BUDGET":
load
'B' & rowno() as [Transaction Index],
fiscal_year,
fiscal_period,
division_key ,
product_key ,
customer_key ,
'B' as [Type Indicator],
units as [Quantity],
value as [Sales Value],
sep as SEP
FROM [..\00 QVD Source\Budget.qvd](qvd)
where fiscal_year = 2015 and fiscal_period >= 10
;
load
'F' & rowno() as [Transaction Index],
fiscal_year,
fiscal_period,
division_key ,
product_key ,
0 as customer_key ,
'F' as [Type Indicator],
[Quantity],
[Sales Value],
SEP
// ,[Trans Date]
FROM [..\00 QVD Source\Forecast.qvd](qvd)
where fiscal_year = 2015 and fiscal_period >= 10
;
left join ("BUDGET")
LOAD
fiscal_year,
fiscal_period,
[Trans Date]
FROM
[..\20 Master Data\Fin to Cal Date Conversion.xls]
(biff, embedded labels, table is Sheet1$);
"BUDGETC":
load
'B' & rowno() as [Transaction Index],
fiscal_year,
fiscal_period,
division_key ,
product_key ,
customer_key ,
'B' as [Type Indicator],
units as [Quantity],
value as [Sales Value],
sep as SEP
FROM [..\00 QVD Source\BudgetCurrent.qvd](qvd)
;
left join ("BUDGETC")
SQL SELECT DISTINCT
a.fiscal_year ,
a.fiscal_period ,
MAX(calendar_key) AS [Trans Date]
FROM dbo.vw_dim_calendar a
inner join dbo.dim_period b
on a.fiscal_year=b.fiscal_year
and a.fiscal_period=b.fiscal_period
where period_key=$(vMaxPeriodkey)
GROUP BY a.fiscal_year,a.fiscal_period,trading_date;
