Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try something like this:
LOAD *
FROM sales_*;
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
Your script take all files.
i want to load only last 6.
Got it, check out the solution provided by Massimo Grossi
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.
Jagan,
Did you mean to type "- index" for offset?
LET vMonth=date(Monthstart(today(), - index), 'YYYYMM');
-Rob
Hi Rob,
Good catch, missed - symbol updated in the post.
Regards,
Jagan.