Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Month Freeze Document

cancel
Showing results for 
Search instead for 
Did you mean: 
MayilVahanan

Month Freeze Document

Last Update:

Sep 21, 2022 1:07:35 PM

Updated By:

Sue_Macaluso

Created date:

Mar 30, 2017 4:21:57 AM

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;

Tags (1)
Comments
pradeep92
Partner - Creator II
Partner - Creator II

Hello Bro,

I am having the similar requirement where I need to freeze the data for each month.

I understand that at first load , the if condition is false and hence SQL is loaded and stored directly into QVD.

2nd time , SQL is loaded and it gets into the if condition and creates a table with MAXIMUM FREEZE DATE which is nothing but TODAY. It is stored in the variable.

and concatenated with previous data available in QVD.

So, we might have the duplicates in this case right. 

 

Please correct me if I am wrong

0 Likes
Version history
Last update:
‎2022-09-21 01:07 PM
Updated by: