topic Re: to delete 1 month data in QlikView

to delete 1 month data

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: to delete 1 month data

krishnacbe — Tue, 11 Apr 2017 18:44:55 GMT

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)

Re: to delete 1 month data

Wed, 12 Apr 2017 01:42:33 GMT

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

Re: to delete 1 month data

Siva_Sankar — Wed, 12 Apr 2017 01:59:25 GMT

Rohit,

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

Data:

LOAD

Field1,

Field2,

Field3,

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

Siva

Re: to delete 1 month data

Wed, 12 Apr 2017 02:27:43 GMT

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.

Re: to delete 1 month data

Siva_Sankar — Wed, 12 Apr 2017 02:38:47 GMT

Rohit,

Find the attached qvw, it works for me.

Siva