Data Reduction for 4 month in QVD data.

[Lakshminarayanan_J]

Dear All,

In my requirement we need to delete last  4 month data in QVD  , but in QVD we have date format in YEARMONTH (202011)

how can i restrict 4 month data based on YEARMONTH without additional field 

scenario is need to delete last 4 month data in QVD and need to load those deleted 4 month data from table .

Kindly help me on it.

Thanks in Advance! 

[rubenmarin]

Hi, you can load a table with all the differnt values you want to keep for YearMonth, this values could be loaded from qvd or from another funtions, in example, to generate a date for the last 12 months:

LOAD Date(MonthStart(AddMonths(Today(),-RecNo())),'YYYYMM') as YEARMONTH
AutoGenerate 12;

Then you can load load from qvd using exists to only load the values loaded in the first table:

LOAd * From [qvd.qvd] Where exits(YEARMONTH);

And then store again the qvd.

I would recommend to a backup of the qvd before testing, also note that filtering by exact dates may need both fields to be in the same format, so maybe it needs some adjustments.

Anthoer option would be load the diffreent values in the qvd, and use min or max to get the 4th value, and use this value in the where clause, like:

tmpYearMonth:
LOAd MIN(YEARMONTH,4) as YEARMONTH FROM [qvd.qvd](qvd);
LET vYearMonth = Peek('YEARMONTH',0,'tmpYearMonth');
DROP Table tmpYearMonth;

qvd:
LOAD * from [qvd.qvd](qvd) Where YEARMONTH>$(vYearMonth);