Qlik Community

QlikView Documents

Documents for QlikView related information.

Month Freeze Document

Month Freeze Document

Hi Folks,

If any one interest to freeze all the data by month wise from sql and append it in the qvd to verify the changes per each month. It will help it.

Let vQVDPath = 'D:\';

Let vFreezeTableName = 'MONTH_FREEZE';

LET vNow = Now();

//GFIR

$(vFreezeTableName):

LOAD *,

  '$(vNow)' AS MonthFreezeTime,

  Floor(Today()) AS MonthFreezeDate,

  Floor(MonthEnd(Today(), -1)) AS MonthFreezeDataUptoDate;

SQL Select * from tablename;

//Check the qvd is exists or not

  LET vListQVDExists = not isnull(QVDCreateTime('$(vQVDPath)\$(vFreezeTableName).qvd'));

  //If exists , concatenate with existing qvds

  If($(vListQVDExists)) then

  //Find the maximum date

     MaxFreezeDate:

     LOAD Max(MonthFreezeDate) AS MaxFreezeDateNum Resident $(vFreezeTableName);

     //Get the maximum date_num

     Let vMaxFreezeNum = Num(Peek('MaxFreezeDateNum',-1, 'MaxFreezeDate'));

     //Drop the temporary table

     DROP Table MaxFreezeDate;

     

  Concatenate($(vFreezeTableName))

  LOAD * From

  $(vQVDPath)\$(vFreezeTableName).qvd(qvd)

  Where MonthFreezeDate <  $(vMaxFreezeNum);

  

  ENDIF;

//Store freeze data into qvd

STORE $(vFreezeTableName) into $(vQVDPath)\$(vFreezeTableName).qvd(qvd);

//Drop the temporary table

DROP Table $(vFreezeTableName);

//Exit script

EXIT Script;

Version history
Revision #:
1 of 1
Last update:
‎03-30-2017 04:21 AM
Updated by: