Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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