Join with range and logical conditions

[BartVA]

I have a table with Rebates, and a table with Sales (see below). In the load editor I need to add to the Rebates table, per record, the total amount of sales that correspond to that rebate.

The Rebates table contains logical descriptions of the conditions ("equals", "not equals", "all", ...), which complicates the matter. Below an overview of the tables with the possible values of the problematic fields:

Doing a join and adding a field with logic works (see below), but the join results in an enormous quantity of records and takes forever to load... What strategy would be better?

tRebate:

Load * Inline [

Cust_ID, RebateType, StartDate, EndDate, BrandOperator, BrandApplicableOn, InvTypeApplicableOn

1, year, 10, 20, equals, all, CI

1, year, 10, 50, equals, BrandA, all

1, publ, 5, 7, not equals, BrandB, all

2, year, 8, 15, equals, all products, CC

];

Join

tSales:

Load * Inline [

Cust_ID, SalesBrand, SalesInvType, Amount, SalesOrderDate

1, BrandA, CC, -1000, 13

1, BrandA, CI, 700, 13

1, BrandB, CI, 2000, 13

1, BrandC, CI, 3000, 13

1, BrandA, CI, 2000, 30

2, BrandA, CC, -300, 9

2, BrandA, CI, -500, 12

2, BrandA, CC, -300, 20

];


NoConcatenate

tFinal:

Load

 *,

    If(SalesOrderDate >= StartDate and SalesOrderDate <= EndDate,

      IF(SalesInvType = InvTypeApplicableOn OR InvTypeApplicableOn = 'all',

        IF(BrandOperator = 'equals',

          IF(SalesBrand = BrandApplicableOn OR BrandApplicableOn = 'all products',

          Amount, 0),

          IF(SalesBrand <> BrandApplicableOn,

          Amount, 0)

        ),0)

     ,0) as CorrectedAmount;

Load * Resident tRebate;

Drop Table tRebate;

(The next step would be a Group By with Sum(CorrectedAmount), but that's not the problem so I didn't work that out here.)

Many thanks in advance for any advice given!

[marcus_sommer · Accepted solution]

Better as creating a (nearly complete) cartesian product between the dim and the facts would be to do the essential expanding only within the dim and then mapping it into the facts, for example:

t: load *, date(StartDate + iterno() - 1) as Date
from DIM while StartDate + iterno() - 1 <= EndDate;

m: mapping load Cust_ID & '|' & Date, BrandOperator & '|' & BrandApplicableOn
resident t;

load *, if(... YourLogic ....) as Result;
load *,
  subfield(applymap('m', Cust_ID & '|' & SalesOrderDate, '#NV'), '|', 1) as BrandOperator,
  subfield(applymap('m', Cust_ID & '|' & SalesOrderDate, '#NV'), '|', 2) as BrandApplicableOn
from FACTS;

[Peter-C]

Im not sure if i understand the problem correctly, but would it be possible to join the rebates into the fitting sales lines (where e.g. the brand and the other factors match) such that the resulting table isnt as large as the cartesian product? Heavily depends on the data though if that is at all feasible. 

[BartVA]

Yes, interesting thought, thanks! I don't think this will be possible with the data I have though.

[BartVA]

Very interesting approach, thanks!