Snapshot QVDs of Fact Tables for each month for last 12 months

[dmohanty]

Hi Team

I have some fact tables where I need to build Snapshots QVDs stored for each month and for last 12 months, based on a field called 'Row Update Time stamp'. Could some one please help in this?

More details below:

For example, the fact table is named as XYZ_FACT and we have multiple fields including ROW_UPDATE_TMSTMP (data format is 1/22/2014 00:49:23.000000). There are records for all 12 months and for last few years from today.

Using the ROW_UPDATE_TMSTMP, we need to build Snapshot QVDs of the single fact table XYZ_FACT stored as 12 QVDs  for last 12 months (last 1 year).

In my current scenario, I should have Snapshot QVDs like this below:

XYZ_FACT_Jan_2014.qvd

XYZ_FACT_Feb_2014.qvd

XYZ_FACT_Mar_2014.qvd

.

.

.

XYZ_FACT_Dec_2014.qvd

From next month on wards (Feb 2015), a new QVD will be generated as XYZ_FACT_Jan_2015.qvd and so on.

Regards!!

[Peter_Cammaert]

This is a construct that'll help you writing the script code. I'm sure that you as a partner will be able to add the missing pieces. I'm assuming that the example table XYZ_FACT is already loaded and still resident.

First set a few variables, for example:

SET NO_OF_MONTHS = 12;

LET vStartDate = AddMonths(MonthStart(Today()), -$(NO_OF_MONTHS));

Then add a loop that runs from 0 to NO_OF_MONTHS - 1. The body consists of:

  IF the QVD for the next month doesn't exist yet (use FileTime()), then proceed:

    Use AddMonths() to set the variable vNextMonthDate to (vStartDate + Loop Variable)

    Create a temporary table that contains all XYZ_FACT rows that are in the month of vNextMonthDate.

        (Use function InMonth() in the WHERE clause to check this.)

    IF the temporary table contains any records at all (use function NoOfRows()), then do the following

        STORE the temporary table in the appropriately named QVD file

        DROP the temporary table

    END IF

  END IF

NEXT

Clear all superfluous variables.

That's it. During the first run, the code will create NO_OF_MONTHS QVD's if and only if there is data for them in the original Facts table. Then, for about 30 days if you start on the first of the month, the script won't do anything. It will not rewrite any existing QVD's. Also, it won't create the QVD for the current month. On the first of the next month (or a later date if this job isn't scheduled to run on the first) it will create a QVD with the last months data.

If something isn't entirely clear, ask away

Best,

Peter

[Anonymous]

Hi,

Modify below script if needed and try:

//Monthly QVD:

TempTable:
LOAD  Distinct Month(Date) AS Month
FROM Table
Order by Month(Date) asc;

NoConcatenate

Table:
Load Month,
     Min(Month) as minMonth,
     Max(Month) as maxMonth
Resident TempTable
Group By Month;

Let vMinMonth=Peek('minMonth', 0, 'Table');
Let vMaxMonth=Peek('maxMonth', 0, 'Table');

FOR i=$(vMinMonth) to $(vMaxMonth)
LET vMonth = Peek('Month',$(i),'Table');


MonthWise:
NoConcatenate
LOAD * FROM Table
Where Month(Date) = '$(vMonth)';

STORE MonthWise into TableName_$( vMonth).qvd;
DROP Table MonthWise;

Next

DROP Table TempTable;
DROP Table Table;

Regards

Neetha

[dmohanty]

Hi Peter,

Thank you for a elaborated explanation, but somehow could you please help in writing the below section that you have just shared above. How to use the InMonth() in WHERE Clause.

Create a temporary table that contains all XYZ_FACT rows that are in the month of vNextMonthDate.

        (Use function InMonth() in the WHERE clause to check this.)

It will be some more help.

[jagan · Accepted solution]

Hi,

Try like this

TempTable:
LOAD  Distinct MonthName(MonthStart(Date)) AS Month
FROM Table

ORDER BY Date;

FOR i= 1 to NoOfRows('TempTable')
LET vMonth = Peek('Month', $(i), 'TempTable');


MonthWise:
NoConcatenate
LOAD * FROM Table
Where MonthName(Date) = '$(vMonth)';

STORE MonthWise into TableName_$( vMonth).qvd;
DROP Table MonthWise;
Next

DROP Table TempTable;

Hope this helps you.

Regards,

Jagan.

[dmohanty]

Hi Jagan,

Thank you for the help here.

However I am getting a Garbage after expression on 'ORDER BY' statement. Any reason why?

[jagan]

Hi,

Remove Order by and then try. 

Regards,

Jagan.