Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

zagzebski
Contributor

Using a loop in a script (with variables)


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;

Tags (2)