Loop procedure

Thu, 12 Sep 2013 20:31:21 GMT

Hi Folks!!!

I have this membership data base that I pull out the info on QlikView. The thing is that every time I reload the QVD erase and replace the info with the new one, what is fine until now. I have to find the way that see the members that where active for each month from Jan 2012 to present. I have a "Eff_Date" field to identify when a member star and a "End_Date" field for when they gone. I also have a "Status" field that identify if the members is active o inactive. I have to create a script to count the member every month that where active with me yn order to create a list by month of the members that where active. Any help???? Thanks

Re: Loop procedure

fernando_tonial — Fri, 13 Sep 2013 01:20:36 GMT

Hi, Try this.

 SET vPath = 'C:\Dir';

LET vStartMonth = Date('01/01/2012');

LET vEndMonth = Date(MonthEnd(Today())-2);



TMP_DATE:

LOAD 

 Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMMDD') AS StartDay,

 Date(MonthEnd(AddMonths('$(vMesInicial)',RowNo()-1)),'YYYYMMDD') AS EndDay,

 Date(AddMonths('$(vMesInicial)',RowNo()-1),'YYYYMM') AS YearMonth

AutoGenerate(Ceil((vEndMonth-vStartMonth)/30.5));



FOR D=1 TO (NoOfRows('DATAS'))



 LET vStartDay = Peek('StartDay',$(D)-1);

 LET vEndDay = Peek('EndDay',$(D)-1);

 LET vYearMonth = Peek('YearMonth',$(D)-1);



 Table:

 SQL Select * From Table 

 where Data >= $(vStartDay)

   and Data <= $(vEndDay);


 Store Table into $(vPath)Table_$(vYearMonth).QVD;

 Drop table Table;



Next



DROP Table TMP_DATE;

Best Regads.

Tonial

topic Re: Loop procedure in QlikView

Loop procedure

Re: Loop procedure