Combine Date IntervalMatch

Thu, 21 Apr 2011 17:52:15 GMT

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;

Combine Date IntervalMatch

Mon, 25 Apr 2011 13:56:32 GMT

Anyway have an idea of how to join these dates together?

Combine Date IntervalMatch

Oleg_Troyansky — Mon, 25 Apr 2011 14:09:53 GMT

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.

Combine Date IntervalMatch

Mon, 02 May 2011 20:58:00 GMT

I can not give the fields new names. Otherwise this will create a circular reference in my data model.

topic Combine Date IntervalMatch in QlikView

Combine Date IntervalMatch

Combine Date IntervalMatch

Combine Date IntervalMatch

Combine Date IntervalMatch