Qvd splitting based on month in incremental load

[Anonymous]

Hello All,

I have a one QVD called Monthly.QVD and it is getting added data to this QVD on Every end of the Month.

I want to split this QVD into two QVD's on month basis. In one QVD i have to see only latest 6 months(from max month to last 6 months) data Monthly.QVD and rest of the months data should go to next QVD as Archived.QVD

I have date column like DD/MM/YYYY hh:mm:ss:0000.

Please help me how to do this task.

Regards,

Kumar

[Anonymous]

You can create Two qvds like?

Latest6Month:

Load *,

Date

From TableName

where Month(Date)>6;

ArchivedMonth:

Load *,

Date

From TableName

where Month(Date)<=6;

Hope this will help!!

[PradeepReddy]

try something like this..

Historic_Data:

Load *,

DATE_FIELD

From QVD_Latest_DATA.QVD where Date(DATE_FIELD)>=6;

Concatenate (Historic_Data)

Load *

FROM QVD_Historic_Data.QVD;

STORE Historic_Data into QVD_Historic_Data.QVD(QVD);

drop table Historic_Data;

Latest_DATA:

Load *,

DATE_FIELD

From DB_Table where DATE_FIELD>= Last_Reload_Time and DATE_FIELD<=Current_Execution_Time;

Concatenate (Latest_Data)

Load *,

DATE_FIELD

From QVD_Latest_DATA.QVD where Date(DATE_FIELD)<6;

STORE Latest_DATA into QVD_Latest_DATA.QVD(QVD);

drop table Latest_DATA;

[#4]

Without knowing your QVD load I would do this on the Monthly.QVD. The ideal is to do this within the monthly.QVD but this will give you what you want if your monthly QVD is actually a QVD with all date information done monthly i.e. there is a month and a year

MinMaxDates:

LOAD

     Max(Date) as MaxDate

RESIDENT Monthly;

LET zMaxDate = Peek('MaxDate');

DROP TABLE MinMaxDates;

[LATEST6MONTHS]:

LOAD *

RESIDENT Monthly

WHERE Date >= Date(MonthStart(AddMonths($(zMaxDate) ,-6)));

STORE [LATEST6MONTHS] into 'Path\Monthly.qvd' (qvd);

DROP TABLE [LATEST6MONTHS];

[ARCHIVED]:

LOAD *

RESIDENT Monthly

WHERE Date < Date(MonthStart(AddMonths($(zMaxDate) ,-6)));

STORE [ARCHIVED] into 'Path\Archived.qvd' (qvd);

DROP TABLE [ARCHIVED];