topic Re: While Extracting Data in QlikView

While Extracting Data

nihhalmca — Mon, 11 Aug 2014 13:30:46 GMT

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.

Re: While Extracting Data

Mon, 11 Aug 2014 13:34:42 GMT

Try something like this:

LOAD *

FROM sales_*;

Re: While Extracting Data

maxgro — Mon, 11 Aug 2014 13:40:37 GMT

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');

Re: While Extracting Data

nihhalmca — Mon, 11 Aug 2014 14:38:47 GMT

Your script take all files.

i want to load only last 6.

Re: While Extracting Data

Mon, 11 Aug 2014 15:18:21 GMT

Got it, check out the solution provided by Massimo Grossi

Re: While Extracting Data

jagan — Tue, 12 Aug 2014 04:31:41 GMT

Hi Nihhal,

Try like this

FOR index = 0 TO 5

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

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

Regards,

Jagan.

Re: While Extracting Data

rwunderlich — Tue, 12 Aug 2014 04:42:12 GMT

Jagan,

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

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

-Rob

Re: While Extracting Data

jagan — Tue, 12 Aug 2014 05:29:14 GMT

Hi Rob,

Good catch, missed - symbol updated in the post.

Regards,

Jagan.