topic Period by Day in QlikView

Period by Day

Fri, 30 Apr 2010 08:58:51 GMT

Hi every one,

I have to create a script which create QVD files since a Database table, but I must create a QVD by day in the Data base:

for this I have a function which work for create QVD by Month :

sub getHistoInfo(path, prefix, dateField,nbDaysBefore)

 // Sets the default first date of the history
 let minDate=num(date('$(startHistoryDate)'));

 trace 'getHistoInfo minDate ' & $(minDate);

 // Gets the last date gathered into qvds
 CALL GetLastDate(path, prefix, '$(dateField)', lastDate);
 trace 'GetLastDate lastDate ' & $(lastDate);



 // The last date become the newest minDate
 unless isnull(lastDate) let minDate=num(date('$(lastDate)'))+1; // minDate=lastDate+1

 // Historic date management.(evol : 23/03/2010)

 if (isnull(lastDate)=-1 ) then
 let lastDate='$(startHistoryDate)';
 trace 'lastDate' & $(lastDate);
 endif

 if ( (num(lastDate)=minDate) or (minDate=num(date('$(startHistoryDate)'))) ) then
 let x=0;
 let RefDate=lastDate;
 else
 if(nbDaysBefore>0) then
 let x=nbDaysBefore;

 let RefDate=num(lastDate) - x;
 let minDate=num(monthstart(date(minDate -x,'DD/MM/YYYY')));
 end if
 endif

trace 'RefDate' & $(RefDate);

 // Set the last date to today
 let maxDate=num(date(today(2)));


 // Calculate the # of days between min and max dates
 let nbDays=maxDate-minDate;

 // Calculate the # of months between min and max dates
 if year(maxDate)=year(minDate) then
 let nbiter=month($(maxDate))-month($(minDate))+1;
 else
 let nbiter=month($(maxDate))+(12-month($(minDate))+1)+12*((year(maxDate)-1)-year(minDate));
 endif

 // Drop histoPieces if already exists as resident
 unless isnull(NoOfRows('_histoPieces')) DROP TABLE _histoPieces;

 // Generates a table containing all the monthly periods between min and max date
 _histoPieces:
 load
 if(recno()=1, date($(minDate)), monthstart(addmonths($(minDate), recno()-1))) as monthstart,
 monthend(addmonths($(minDate), recno()-1)) as monthend,
 autogenerate ($(nbiter));
end sub

And in the script :

call getHistoInfo('$(pathQVD)MVT', 'MVT_$(DBName)', 'date',0);
// only for test
//___________________
//set nbiter=3;
//___________________

for i=0 to $(nbiter)-1

let startdate=peek('monthstart',i,'_histoPieces');
let enddate=date(num(peek('monthend',i))+1);
let monthdate=num(month(peek('monthstart',i,'_histoPieces')),'00');
let daydate=num(day(peek('monthstart',i,'_histoPieces')),'00');
let yeardate=year(peek('monthstart',i,'_histoPieces'));

let strTrace='*** Current period : >=' & '$(startdate)' & ' and <' & '$(enddate)';
trace $(strTrace);

And afetr a Store the table selected in the QVD with the name of the periode.

It work perfectly for period by month but I don't understand how I can modifie this function to selected for each day a QVD.

Can you help me please?

Period by Day

Fri, 30 Apr 2010 13:40:28 GMT

I have modify my function like this but it's works for the 10 fist days, but after the 10/09/2009 any date is return.

sub getHistoInfoByDay(path, prefix, dateField,nbDaysBefore)

 // Sets the default first date of the history
 let minDate=num(date('$(startHistoryDate)'));

 trace 'getHistoInfo minDate ' & $(minDate);

 // Gets the last date gathered into qvds
 CALL GetLastDate(path, prefix, '$(dateField)', lastDate);
 trace 'GetLastDate lastDate ' & $(lastDate);



 // The last date become the newest minDate
 unless isnull(lastDate) let minDate=num(date('$(lastDate)'))+1; // minDate=lastDate+1

 // Historic date management.(evol : 23/03/2010)

 if (isnull(lastDate)=-1 ) then
 let lastDate='$(startHistoryDate)';
 trace 'lastDate' & $(lastDate);
 endif

 if ( (num(lastDate)=minDate) or (minDate=num(date('$(startHistoryDate)'))) ) then
 let x=0;
 let RefDate=lastDate;
 else
 if(nbDaysBefore>0) then
 let x=nbDaysBefore;

 let RefDate=num(lastDate) - x;
 let minDate=num(date(minDate -x,'DD/MM/YYYY'));
 end if
 endif

trace 'RefDate' & $(RefDate);

 // Set the last date to today
 let maxDate=num(date(today(2)));


 // Calculate the # of days between min and max dates
 let nbiter=maxDate-minDate;

 // Drop histoPieces if already exists as resident
 unless isnull(NoOfRows('_histoPieces')) DROP TABLE _histoPieces;

 // Generates a table containing all the daily periods between min and max date
 _histoPieces:
 load
 if(recno()=1, date($(minDate)), date($(minDate)+recno())) as monthstart,
 ($(minDate)) as monthend
 autogenerate ($(nbiter));
end sub

You have a idea why please ?

Period by Day

Mon, 03 May 2010 14:14:08 GMT

My new version of the code :

With Month-CHrun a column calculded with ID

((sum({ $<[Date]={">=$(=monthstart([Month-Churn])) <=$([Month-Churn])"}, Month= ,Year=,Week= >} [USER.Nb user ID]))
-(sum( { $<[Date]={">=$(=monthstart(max([Date])))<=$(=max([Date]))"}>}[USER.Nb user ID]))
+(sum( { $<USER.Cohorte={">=$(=monthstart(max([Date])))<=$(=max([Date]))"}>} [USER.Nb user ID])))
/
(sum({ $<[Date]={">=$(=monthstart([Month-Churn])) <=$([Month-Churn])"}, Month= ,Year=,Week= >} [USER.Nb user ID]))

I have a NULL value I don't know why can you help me please?