Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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