While Extracting Data

[nihhalmca]

Hi All,

I need to extract last 6 months data from source (source has may files like sales_201301, sales_201302............).

Suppose i want to get all files at a time i can do but i want to extract latest 6 files dynamically, please let me know how can i achieve this task.

Regards,

Nihhal.

[#2]

Try something like this:

LOAD *

FROM sales_*;

[maxgro]

for last 6 months files

let thismonth=date(today(), 'YYYYMM');

for i=1 to 6

  trace $(thismonth);

  load * from sales_$(thismonth).qvd (qvd);

  // subtract a month

  LET thismonth = date(AddMonths(Date#($(thismonth) & '01', 'YYYYMMDD'), -1), 'YYYYMM');

next

[nihhalmca]

Your script take all files.

i want to load only last 6.

[#5]

Got it, check out the solution provided by Massimo Grossi

[jagan]

Hi Nihhal,

Try like this

FOR index = 0 TO 5

     LET vMonth=date(Monthstart(today(), -index), 'YYYYMM');   

     load * from sales_$(vMonth).qvd (qvd);  

NEXT

Regards,

Jagan.

[rwunderlich]

Jagan,

Did you mean to type "- index" for offset?

LET vMonth=date(Monthstart(today(), - index), 'YYYYMM');

-Rob

[jagan]

Hi Rob,

Good catch, missed - symbol updated in the post.

Regards,

Jagan.