0 Replies Latest reply: Nov 5, 2013 4:26 PM by Steve Zagzebski RSS

    Using a loop in a script (with variables)

    Steve Zagzebski


      I have the following script. While I included all of it the only thing I really want to accomplish is write this script once and loop through it for every month from JAN2010 through OCT2013. I have noted the variables (in red) and the table name I have created but would need to change each time I loop through.  Currently I create this same script 40+ times to get it for each month through 2013!!

      For instance on the second loop though JAN2010WHERE would become FEB2010WHERE, JAN2010RETENSION would become FEB2010RENTENSION, and the table name Line_of_BusinessCube_Jan2010 would become Line_of_Business_Feb2010 and so on for each successive month.

       

      ProductionByLine_of_BusinessByCust:LoadLine_of_Business as [Line_of_Business Cube Pre],sum([Commission]) as [Commission Line_of_Business Cube Pre] Resident Production
          
      Where
         
      $(JAN2010WHERE)
                
      Group By [Line_of_Business]
                
      Order by Line_of_Business ASC;
      left Join(ProductionByLine_of_BusinessByCust)LoadLine_of_Business as [Line_of_Business Cube Pre],Customer_Name as [Cust Cube Pre],FirstValue(ID|Customer) As ID|Customer,sum([Commission]) as [Commission Line_of_BusinessCust Cube Pre] Resident Production
          
      Where
          
      $(JAN2010WHERE)Group By [Line_of_Business],Customer_NameOrder By Line_of_Business ASC;

      Line_of_BusinessCube_Jan2010:

      Load [Line_of_Business Cube Pre]&'|'&[Cust Cube Pre]&'|'& $(JAN2010RETENSION) as Key|RetentionLine_of_Business,[Line_of_Business Cube Pre],ID|Customer,MakeDate(2010,01,01)as [Retention Date],[Commission Line_of_BusinessCust Cube Pre],[Commission Line_of_Business Cube Pre],if(previous([Line_of_Business Cube Pre])=[Line_of_Business Cube Pre],rangesum([Commission Line_of_BusinessCust Cube Pre],peek([CumCommission Line_of_Business Cube])),[Commission Line_of_BusinessCust Cube Pre]) as [CumCommission Line_of_Business Cube],if(previous([Line_of_Business Cube Pre])=[Line_of_Business Cube Pre],rangesum([Commission Line_of_BusinessCust Cube Pre],peek([CumCommission Line_of_Business Cube])),[Commission Line_of_BusinessCust Cube Pre])/[Commission Line_of_Business Cube Pre] as  [Commission Line_of_Business CummPerc Cube]Resident ProductionByLine_of_BusinessByCustOrder by [Line_of_Business Cube Pre] asc, [Commission Line_of_BusinessCust Cube Pre] desc;

      Drop Table ProductionByLine_of_BusinessByCust;