Using a loop in a script (with variables)

zagzebski — Tue, 05 Nov 2013 21:26:49 GMT

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;

topic Using a loop in a script (with variables) in QlikView

Using a loop in a script (with variables)