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

[robert_mika]

How did you define your variable?

Try:

$(v2)

[mhmmd_srf]

Hi Robert,

this is not working.

Last_Month:

Load

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

  AutoGenerate 26;

Set v1=Max([Prior_Months]);

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

[robert_mika]

How did you define v1 then?

[mhmmd_srf]

Hi Robert,

Below is the script.

Last_Month:

Load

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

  AutoGenerate 26;

Set v1=Max([Prior_Months]);

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

Thanks,

Sarif

[robert_mika]

You need to use this method to get the max value for v1

Temp;

Load

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

  AutoGenerate 26;

Let v1= Num(Peek('Prior_Months', 0, 'Temp')); 

drop table Temp;

[mhmmd_srf]

okk...but I have a question here..

Let v1= Num(Peek('Prior_Months', 0, 'Temp'));   and

Set v1=Max([Prior_Months]);   ... both cases v1 returns same value.

Could you please explain me what is the difference between those. I have used Let v1= Num(Peek('Prior_Months', 0, 'Temp')); kind of expression earlier but blindly.

It would be great if you can explain me.

Thanks a lot...

Sarif

[robert_mika]

LET will evaluate the expression

SET will assign a string.

In your case

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

will return string only;

Please see below example:

Data:

load * inline [

A

1

2

3

4

5]

;

temp:

load

max(A) as MaxValue

resident Data;

set v1=max(A);

let v2=peek('MaxValue',0,'temp');

Final:

load A as Category

Resident Data

where A<$(v2);

drop table temp,Data;

Result:

Does that help?

[mhmmd_srf]

okk...so in my expression if I use Let v2=Date(addmonths($(v1), -5), 'YYYY-MMM'); instead of Set v2=Date(addmonths($(v1), -5), 'YYYY-MMM'); will that work?

Thanks,

Sarif

[robert_mika]

It should work.

If you still in doubt could you post your application or sample of the data?