Scenario provided:

Thanks for taking a look at this.  What I am trying to achieve is automating Marketing's current manual process of evaluating different combinations given 5 pricing plans and 4 items.  This is not an audit test but more of a process efficiency initiative.  We are implementing CCM using ACL as the engine and I was hoping to enhance ACL's reputation by delivering on this process improvement.  This project therefore has no deadline so I have ample time to work on it but I have to eventually get it to work.

The company has over 1,000 products and have 30 pricing plans give or take a few.  Pricing plans are added whenever a new sales agreement is finalize with a big customer and also deleted when it ends without being renewed.

Periodically, Marketing comes up with special promotions that bundles together various items at a single price and makes this available to anyone.  The bundling process is subject to the following rules:

1.  Only prices in a current active price plan can be used.
2.  Bundles should meet a target price (e.g. $99 or $999, etc.)
3.  Bundles should meet the minimum gross margin impost on promotional activities.

To simplify the process, Marketing agreed to the following:

1. Limit the number of pricing plans being evaluated to 30.
2. Limit the number of items to 4. (I actually suggested 4 since it can be scaled up to 8, 12, etc. by running the Script again using 4 different items.)

Here is an example:

Price_Plan     Item_1     Item_2
1               Price1          Price2             
2               Price3          Price4

Combinations:

Item_1     Item_2     f_Totals
Price1     Price2          Total1
Price1     Price4          Total2
Price3     Price2          Total3
Price3     Price4          Total4

Final Table (assuming I'm only interested in records with Total3):

Item_1     Item_2     f_Totals

Price3     Price2          Total3

The script should therefore pick up the first value of Item_1 which is Price1 and match it with all the prices in Item_2.  Then it picks the second record of Item_2 which is Price3 and then match it with all the prices in Item_2.

The differences with the above example is that we are dealing with 30 pricing plans and 4 items so there is a maximum of 810,000 combinations/records to evaluate.  I say maximum since there are duplicates in pricing plans (e.g. some pricing plans have some items priced the same) and these I have removed in my Temp table.

 

 

 

 

 

COM****************************************************************
COM Written by David Coderre
COM Date March 2008
COM Posted on the ACL User Forum and then again on the TexasACL.COM website.
COM****************************************************************

 

 

SET SAFETY ON
SET ECHO NONE

DELETE ALL OK
DELETE FORMAT comb2 OK
DELETE comb2.FIL OK

DIALOG (DIALOG TITLE "User Dialog" WIDTH 461 HEIGHT 370 ) (BUTTONSET TITLE "&OK;&Cancel" AT 348 24 DEFAULT 1 ) (TEXT TITLE "Combination parameters" AT 24 28 ) (TEXT TITLE "Total" AT 132 280 ) (EDIT TO "v_tot" AT 204 276 WIDTH 54 DEFAULT "1004" ) (TEXT TITLE "cc. CAATS 2008" AT 276 328 ) (TEXT TITLE "Select table to open" AT 48 64 ) (ITEM TITLE "f" TO "v_infile" AT 96 84 WIDTH 222 HEIGHT 169 )
Open %v_infile%

count

v_prod1=blanks(2000)
v_prod2=blanks(2000)
v_prod3=blanks(2000)
v_prod4=blanks(2000)

GROUP
 v_prod1=alltrim(v_prod1)+alltrim(str(Item_1,12))+';'
 v_prod2=alltrim(v_prod2)+alltrim(str(Item_2,12))+';'
 v_prod3=alltrim(v_prod3)+alltrim(str(Item_3,12))+';'
 v_prod4=alltrim(v_prod4)+alltrim(str(Item_4,12))+';'
END

SET LOOP TO 0

v_ctr1=1
v_ctr2=1
v_ctr3=1
v_ctr4=1
v_p1=Item_1
v_p2=Item_2
v_p3=Item_3
v_p4=Item_4

GROUP while recno()=1
  LOOP while v_ctr1<=Count1
  v_p2=Val(split(v_prod2,';',v_ctr2),0)
    LOOP while v_ctr2<=count1
    v_p3=Val(split(v_prod3,';',v_ctr3),0)
      LOOP while v_ctr3<=count1
      v_p4=Val(split(v_prod4,';',v_ctr4),0)
        LOOP while v_ctr4<=count1
        Extract v_p1 as 'Prod1' v_p2 as 'Prod2' v_p3 as 'Prod3' v_p4 as 'Prod4' to comb2
COMMENT if v_p1+v_p2+v_p3+v_p4=val(v_tot,0)
        v_ctr4=v_ctr4+1
        v_p4=val(split(v_prod4,';',v_ctr4),0)
        END
      v_ctr3=v_ctr3+1
      v_p3=val(split(v_prod3,';',v_ctr3),0)
      v_ctr4=1
      END
    v_ctr2=v_ctr2+1
    v_p2=Val(split(v_prod2,';',v_ctr2),0)
    v_ctr3=1
    v_ctr4=1
    END
  v_ctr1=v_ctr1+1
  v_p1=val(split(v_prod1,';',v_ctr1),0)
  v_ctr2=1
  v_ctr3=1
  v_ctr4=1
  END
END

COMMENT Note: If you want to generate all possible combinations and then set you P1+p2+p3+p4=??? afterwards, remove the IF Condition from the EXTACT command.

SET ECHO ON
SET SAFETY ON