How to Load Last 6 months QVD's from QVD folder ?

[madhubabum]

Hi Experts

I am Having Last 3 Years data(2013,2014,2015)

QVD names are as following,

Sales_201201.qvd

sales_201202.qvd

.

.

sales_201506.qvd

sales_201507.qvd

sales_201508.qvd

sales_201509.qvd

sales_201510.qvd

sales_201511.qvd

sales_201512.qvd

From the above data set, i want to load last 6 months QVD's only .....

How we can load the last 6 Months qvd's ?

Note : If Sales_201601.qvd, exist then load (sales_201507,201508,201509,201510,201511,201512,201601)

Please Help to me Write the Script...

Best Regards,

Madhu

[asgardd2]

Hello!

You can use this script:

SET numOfMonths = 6;
CalForLoop:
LOAD
'sales' & Year(AddMonths(today(),-RowNo())) & Num(Month(AddMonths(today(),-RowNo())),'00') as fileName
AutoGenerate($(numOfMonths));
FOR i = 1 to FieldValueCount('fileName')
      let sourceSalesFile = FieldValue('fileName',$(i));
      Sales:
      LOAD
      *
      FROM $(sourceSalesFile);
NEXT
DROP TABLE CalForLoop;

[Chanty4u]

hi,

this thread same as ur question.

How to Load privious qvd?

please chk it

[Anonymous]

Loop in script??

check this:

Loops in the Script

[sunny_talwar]

May be loop through your files.

FOR i = 0 to 5 6

    LET vDate = Date(AddMonths(MakeDate(2016, 1, 1), $(i)), 'YYYYDD');

    Table:

    LOAD *

    FROM sales_$(vDate).qvd (qvd);

NEXT;

UPDATE: Small correction above: Loop from 0 to 6 instead of 0 to 5

[Anonymous]

may be this as well?

Let V1=date(Today(),'YYYYMM');      ////Current Month

Let V2=date(AddMonths(Today(),-1),'YYYYMM');     //Previous Month

And similarly for last 4 month more and then:

Qvd1:

Load *

from sales_$(V1).qvd;

Qvd2:

Load *

from sales_$(V2).qvd;

and so on....

Else Loop is also a option....

[Anonymous]

Sunny,

In your case, as you have mentioned LET vDate = Date(AddMonths(MakeDate(2016, 1, 1), $(i)), 'YYYYDD');

It will go for next coming 6 month not previous 6 months, am I right??

[Anonymous]

I would say, make it from this

FOR i = 0 to 5 6   

to

For i= -5 to 0

[sunny_talwar]

Forgot the negative sign

FOR i = 0 to 6

    LET vDate = Date(AddMonths(MakeDate(2016, 1, 1), -$(i)), 'YYYYDD');

    Table:

    LOAD *

    FROM sales_$(vDate).qvd (qvd);

NEXT;

[sunny_talwar]

That is another option