Promo rebate calculation

[gidon500]

Hello All

my bos wants me to calculate the Promo rebate  that we should get

from out supplier .

Attached is an excel file

top left is a table with promo list

promo no , part , dates( start and end period ) , and reabate amount

top right   an invoice list 

the the date , part , qty 

in the middle down is the output i need

i want to create an  object with   list of invoices with the relevant rebate amount for each line ( part )  .

same part cab get diffrenet rebate for each date  , and may not get at all  .

thanks for your help

gidon

[Sokkorn · Accepted solution]

Hi Gidon,

I assume that your data source something like this

1. Table Promo

PromoNo	PARTNAME	StartDate	EndDate	UnitRebate
PR0000	NWB5UIV	1-11-2011	30-11-2011	100
PR0001	NWB5SIV	1-12-2011	31-12-2011	200
PR0002	NWB53IV	15-11-2011	14-12-2011	300
PR0003	NWB5UIV	5-12-2011	12-12-2011	1000

2. Table Invoice

InvoiceNo.	InvoiceDate	PartNumber	Quantity
IV100	10-11-2011	NWB5UIV	2
IN106	15-11-2011	NWB5SIV	12
IV102	20-11-2011	NWB53IV	10
IV105	02-12-2011	NWB5UIV	4
IV101	05-12-2011	NWB5SIV	5
IV103	10-12-2011	NWB5UIV	7

Use script below

[Temp]:
LOAD PromoNo, 
     PARTNAME, 
     StartDate, 
     EndDate, 
     UnitRebate
FROM [promo-calc1.xlsx]
(ooxml, embedded labels, table is [ promo]);
[tmpMinDate]:
LOAD MIN(DATE([StartDate],'DD-MMM-YYYY'))    AS [MinDate] RESIDENT [Temp];
LET vStartDate = NUM(PEEK('MinDate',0,'tmpMinDate'));
DROP TABLE [tmpMinDate];
// 4 ------------------ Param End Date for create calendar -------------------------------------------
[tmpMaxDate]:
LOAD MAX(DATE([EndDate],'DD-MMM-YYYY'))    AS [MaxDate] RESIDENT [Temp];
LET vEndDate = NUM(PEEK('MaxDate',0,'tmpMaxDate'));
DROP TABLE [tmpMaxDate];
FOR i=vStartDate TO vEndDate
    [DateList]:
    LOAD DATE($(i)) AS [DateList]
    AutoGenerate 1;
NEXT
//*********** Clear Variable ***********
LET vStartDate = NULL();
LET vEndDate = NULL();
LET i = NULL();
//**************************************
[intTable]:
IntervalMatch (DateList)
LOAD 
    DATE([StartDate])    AS [tmpStartDate], 
    [EndDate]        AS [tmpEndDate]
RESIDENT [Temp];
LEFT JOIN ([intTable])
LOAD
    [StartDate]        AS [tmpStartDate],
    [EndDate]    AS [tmpEndDate],
    PromoNo, 
    PARTNAME,  
    UnitRebate
RESIDENT [Temp];
DROP TABLE [DateList];
DROP TABLE [Temp];
[Promo]:
LOAD DISTINCT
    TRIM(PromoNo)    AS [PromoNo], 
    TRIM(PARTNAME)    AS [PARTNAME],
    DATE([DateList],'DD-MM-YYYY')    AS [PromoDate],
    UnitRebate
RESIDENT [intTable];
DROP TABLE [intTable];
//==============
[tmpPromo]:
MAPPING
LOAD 
     PARTNAME &'-'&PromoDate, 
     UnitRebate
RESIDENT [Promo];
[Inv]:
LOAD InvoiceNo., 
     InvoiceDate, 
     PartNumber,
     Quantity,
     APPLYMAP('tmpPromo',TRIM(PartNumber)&'-'&DATE(InvoiceDate,'DD-MM-YYYY'),0)    AS UnitRebate
FROM
[promo-calc1.xlsx]
(ooxml, embedded labels, table is [ Invoices]);
DROP TABLE [Promo];

See attached files also.

HTH and let me know.

Regards,

Sokkorn Cheav

[gidon500]

Hello  Sokkorn ,

it works great  ,I understood  your steps  .

I will put it now in my main script .

and check it

thank you very much for your help It works

Gidon