2 Replies Latest reply: Dec 12, 2011 8:01 AM by gidon david

# Promo rebate  calculation

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

• ###### Re: Promo rebate  calculation

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]:
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)
DATE([StartDate])    AS [tmpStartDate],
[EndDate]        AS [tmpEndDate]
RESIDENT [Temp];

LEFT JOIN ([intTable])
[StartDate]        AS [tmpStartDate],
[EndDate]    AS [tmpEndDate],
PromoNo,
PARTNAME,
UnitRebate
RESIDENT [Temp];
DROP TABLE [DateList];
DROP TABLE [Temp];

[Promo]:
TRIM(PromoNo)    AS [PromoNo],
TRIM(PARTNAME)    AS [PARTNAME],
DATE([DateList],'DD-MM-YYYY')    AS [PromoDate],
UnitRebate
RESIDENT [intTable];
DROP TABLE [intTable];
//==============

[tmpPromo]:
MAPPING
PARTNAME &'-'&PromoDate,
UnitRebate
RESIDENT [Promo];

[Inv]:
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

• ###### Promo rebate  calculation

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