to delete 1 month data

[#1]

Hi,

I am having quick question:

QVD will hold data for 1 year only, if new data has come then older data will be removed.

Eg: Current month is Apr, so my QVD will have the data staring from month May 16 to Apr 17. If my current month is May17 then it should hold data for month June16 to May 17. QVD should not have data for Apr16 month.

Could you please help me.

Thanks,

Rohit

[krishnacbe]

Hi,

Which DB are you using. If you SQL server as DB, try below query to get the Start Date and use it in your where clause.

select dateadd(month,datediff(month, 0, getdate())-12,0)

[#3]

Hi Krishna,

I am not using any DB here. As stated My QVD should have data of last 1 year only.

Also, shared expression is not working here.

Thanks,

Rohit

[Siva_Sankar]

Rohit,

In your load statement, just add a filter condition like below.

Data:

LOAD

  Field1,

  Field2,

  Field3,

;WHERE YourDateField >=MonthStart( AddYears(Today(),-1));

Siva

[#5]

Hi Siva,

In the where condition, WHERE YourDateField >=MonthStart( AddYears(Today(),-1));

the condition MonthStart( AddYears(Today(),-1)) is giving last 12 month date

But it is not working. I am attaching here sample input file and qvw for reference.

[Siva_Sankar]

Rohit,

Find the attached qvw, it works for me.

Siva