Loading Data for last 5 Months

[mhmmd_srf]

Hi All,

I have a qvd with all records from source. I want to restrict data loading for last 5 month on basis of sys_created_on present in QVD and source.

We have a generic Month table called Last_Month.

Load

Date(MonthStart(Today(),-RowNo()+1), 'YYYY-MMM') as Prior_Months

  AutoGenerate 26;

then variable ::  Set v2=Date(addmonths($(v1), -5), 'YYYY-MMM');

And in where clause in loading script is like below:

	FROM [lib://IT Standard Metrics/QVD\Request_Result_QVD.qvd]

(qvd)

where Date( MonthStart( sys_created_on ), 'YYYY-MMM') >= '$(=$(v2))';

But it is loading all records from QVD. However if I use hardcoding as 2016-May instead of variable, it is working fine.

Any clue?

Thanks,

Sarif

[tamilarasu]

Hi Mohammad,

Try this,

Last_Month:

Load Date(MonthStart(Today(),-RowNo()+1), 'YYYY-MMM') as Prior_Months

AutoGenerate 26;

Max_Month:

LOAD Max(Prior_Months) as MaxMonth

Resident Last_Month;

Let v1=Peek('MaxMonth',0,'Max_Month');

Let v2=Date(Addmonths('$(v1)', -5), 'YYYY-MMM');

DROP Table Max_Month, Last_Month;

Where Clause Script:

FROM [lib://IT Standard Metrics/QVD\Request_Result_QVD.qvd]

(qvd)

where Date( MonthStart( sys_created_on ), 'YYYY-MMM')  >= '$(v2))';