Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to combine the quote_date (from the test excel sheet) to the master_date (MasterCalender table) using an interval match or any other technique. Whenever I attempt to combine them I create a synthetic key or if I try to place the 'master_date' and 'Territory_ID' together my other data loads incorrectly. The interval match doubles the quote value and does not actually connect it to the master date.
I was wondering if anyone is able to offer some advice on this or experience? Any assistance would be greatly appreciated.
OnlineOrDirectSales:
MAPPING Load * Inline [
OnlineOrderFlag, OrderTransactionType
0, Direct
-1, Website
];
SalesOrderHeader:
LOAD
APPLYMAP('OnlineOrDirectSales', OnlineOrderFlag) AS OrderTransactionType,
date(OrderDate) AS master_date,
OrderDate,
//autonumber(TerritoryID & '|' & date(OrderDate)) AS DateSalesOrder_Mast_ID,
//AUTONUMBER(OrderDate&'-'& TerritoryID &'-'&'') AS MastKey,
SalesOrderID, //PK
TerritoryID AS Territory_ID;
SQL SELECT *
FROM SalesOrderHeader;
QuotaTemp:
CrossTable (BusinessEntity_ID, Quota)
LOAD date(F1) AS master_date,
[275],
[276],
[277],
[279],
[280],
[282],
[284],
[286],
[288],
[289],
[290],
Website
FROM
(ooxml, embedded labels, table is Quota);
Quota:
LEFT JOIN (SalesTerritoryHistory) LOAD
BusinessEntity_ID AS QBusinessEntity_ID,
date(master_date) AS quota_date,
//AUTONUMBER(master_date&'-' &'-'&'') AS MastKey,
Quota
RESIDENT QuotaTemp;
DROP TABLE QuotaTemp;
//Attempt to put together combine this date
REM IntervalMatch (master_date) LOAD date(quota_date)
Resident SalesTerritoryHistory;
REM JOIN LOAD *
RESIDENT SalesTerritoryHistory;
Anyway have an idea of how to join these dates together?
Jacob,
it doesn't look like you need an INTERVALMATCH to join those two dates... Simply give the two fields identical names, and make sure that you have a Master date for each Order Date.
INTERVALMATCH is used for joining single dates to date ranges (Start - Stop), - it's not your case here.
Also, in the CROSSTABLE load, you need to add one more parameters for a number of "Qualifying Fields" - in your case, 1. THat is most likely the reason your data loads incorrectly.
I can not give the fields new names. Otherwise this will create a circular reference in my data model.