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

    Promo rebate  calculation

    gidon david

      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
          Sokkorn Cheav

          Hi Gidon,

           

          I assume that your data source something like this

          1. Table Promo

          PromoNoPARTNAME StartDateEndDateUnitRebate
          PR0000NWB5UIV1-11-201130-11-2011100
          PR0001NWB5SIV1-12-201131-12-2011200
          PR0002NWB53IV15-11-201114-12-2011300
          PR0003NWB5UIV5-12-201112-12-20111000

           

          2. Table Invoice

          InvoiceNo.InvoiceDatePartNumberQuantity
          IV10010-11-2011NWB5UIV2
          IN10615-11-2011NWB5SIV12
          IV10220-11-2011NWB53IV10
          IV10502-12-2011NWB5UIV4
          IV10105-12-2011NWB5SIV5
          IV10310-12-2011NWB5UIV7

           

           

          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