Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Freezing the data week wise

mayilvahanan
Not applicable

Freezing the data week wise

Hi all,

Below query is used to snapshot the complete data from the database in weekly base. This is used to compare any change in existing data in database and compare the snapshot each and every week like sales, payment, etc.

Hope it will help anyone.

First time, it create new qvd later on, its concatenate the values with existing qvd.

LET vFreezeDay =  'Fri'; // Freeze Day

LET vFreezeTime = 16; // Freeze Hour (24 hours)

LET QVDFreeze = 'D:\'; //Qvd path

//Check the day and hrs

IF WeekDay(ConvertToLocalTime(UTC(), 'GMT+08:00')) = '$(vFreezeDay)'  AND Hour(ConvertToLocalTime(UTC(), 'GMT+08:00')) = $(vFreezeTime) Then

  //Store the Table Name into a variable

  LET vFreezeTableName = 'WEEK_FREEZE';

  //Load only the current week data

  $(vFreezeTableName):

  LOAD

  *,

  $(vMinAPInvoiceCalendarWeekYear) AS FREEZE_CALENDAR_WEEK_YEAR,

  Floor(WeekName(Today())) AS  FREEZE_WEEK_NUMBER;

  SQL Select * from tableName;

  //Check the qvd is exists or not

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

  //If exists , concatenate with existing qvds

  If($(vListQVDExists)) then

  //Find the maximum week number

     MaxFreezeWeekDate:

     LOAD Max(FREEZE_WEEK_NUMBER) AS MaxFreezeWeekNumber Resident $(vFreezeTableName);

     //Get the maximum week_num

     Let vMaxFreezeWeekNum = Num(Peek('MaxFreezeWeekNumber',-1, 'MaxFreezeWeekDate'));

     //Drop the temporary table

     DROP Table MaxFreezeWeekDate;

    

  FinalTable:

  NoConcatenate

  LOAD * Resident $(vFreezeTableName);

  Concatenate

  LOAD * From

  $(QVDFreeze)$(vFreezeTableName).qvd(qvd)

  Where FREEZE_WEEK_NUMBER <  $(vMaxFreezeWeekNum);

  

  //Store freeze data into qvd

  STORE FinalTable into $(QVDFreeze)$(vFreezeTableName).qvd(qvd);

  //Drop the temporary table

  DROP Table FinalTable;

  

  ELSE

  //Store freeze data into qvd

  STORE $(vFreezeTableName) into $(QVDFreeze)$(vFreezeTableName).qvd(qvd);

  ENDIF;

//Drop the table

  DROP Table $(vFreezeTableName);

ENDIF;

Labels (2)
Version history
Revision #:
1 of 1
Last update:
‎03-07-2016 05:59 AM
Updated by: