topic Re: How to Load Last 6 months QVD's from QVD folder ? in QlikView

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

madhubabum — Mon, 11 Jan 2016 10:58:45 GMT

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

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

asgardd2 — Mon, 11 Jan 2016 11:12:03 GMT

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;

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

Chanty4u — Mon, 11 Jan 2016 11:21:18 GMT

hi,

this thread same as ur question.

How to Load privious qvd?

please chk it

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

Anonymous — Mon, 11 Jan 2016 11:22:42 GMT

Loop in script??

check this:

Loops in the Script

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

sunny_talwar — Mon, 11 Jan 2016 11:25:06 GMT

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

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

Anonymous — Mon, 11 Jan 2016 11:28:52 GMT

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....

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

Anonymous — Mon, 11 Jan 2016 11:33:31 GMT

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??

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

Anonymous — Mon, 11 Jan 2016 11:35:30 GMT

I would say, make it from this

FOR i = 0 to 5 6

For i= -5 to 0

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

sunny_talwar — Mon, 11 Jan 2016 11:35:52 GMT

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;

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

sunny_talwar — Mon, 11 Jan 2016 11:36:20 GMT

That is another option

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

PradeepReddy — Mon, 11 Jan 2016 11:51:26 GMT

Please see the attachment.. it might be helpful for you..

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

Kushal_Chawda — Mon, 11 Jan 2016 12:27:09 GMT

If you need dynamic solution you can follow the below approach

let vQVDPath ='D:\Qlik\QVD; // Define your QVD folder path

for Each vFile in FileList('$(vQVDPath)\*.qvd')

FileName:

LOAD subfield(subfield(SubField('$(vFile)','\',-1),'.',1),'_',2) as FileName

AutoGenerate(1);

NEXT vFile

MaxMonth:

LOAD MaxMonth - IterNo()+1 as Last_6_Months

While IterNo() <=7;

LOAD max(FileName) as MaxMonth

Resident FileName;

DROP Table FileName;

Final:

LOAD * Inline [

junk ];

for i=1 to FieldValueCount('Last_6_Months')

let vMonth = FieldValue('Last_6_Months',$(i));

Concatenate(Final)

LOAD *,

FileBaseName() as FileName

FROM

[$(vQVDPath)\Sales_$(vMonth).qvd]

(qvd);

NEXT i

DROP Field junk;

DROP Table MaxMonth;