topic Re: How to fetch data from splited qvds in QlikView

How to fetch data from splited qvds

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

In my application there is one table which is fetching data from one qvd.but now scenario has been changed due to data overload that qvd has been splitted into different qvds year wise.Now I wanted to fetch that data from splitted qvds.

Earlier Scenario

E.g

LOAD

K1,

FROM .......KK.QVD;

Current Scenario

LOAD,

K1,

FROM.?????

Regards,

celambarasan‌sli‌qlikrajan‌New to QlikView‌

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 14:21:43 GMT

you can use * or loop function to load a group of qvds. for example if you have abcfile2003, abcfile2004, and abcfile2005 in a folder you can write load * from abcfile*.qvd. This is the simplest way to load otherwise you can use loop also. Let me know if you need help on loading through loop.

Rajan

Re: How to fetch data from splited qvds

buzzy996 — Wed, 15 Feb 2017 14:23:20 GMT

try tis way,

LOAD

K1,

FROM .......KK2017.QVD;

concatenate LOAD

K1,

FROM .......KK2016.QVD;

HTH

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 14:25:20 GMT

In my view writing 10 different load statements and concatenating is not a great idea.

Re: How to fetch data from splited qvds

Wed, 15 Feb 2017 14:26:10 GMT

Do like this

LOAD,

K1,

FROM K*qvd(qvd);

k* will load qvds starting with K

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 14:28:59 GMT

Loop code:

sub DoDir (Root)

for each Ext in 'qvd'

for each File in filelist(Root & '\abc*.' & Ext)

Temp:

LOAD Distinct [Shipment ID],

[BL/AWB/PRO],

Container,

[Container Type],

[BL Origin],

[BL Dest],

POL

FROM

[$(File)]

(ooxml, embedded labels);

Next File

Next Ext

for each Dir in dirlist (Root & '\Port Operations*.' & Ext)

call DoDir(Dir)

Next Dir

end sub

call DoDir ('path where multiple qvds stored')

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 14:31:34 GMT

remove (ooxml, embedded labels); that's for excel. Let me know if any issues you get

Re: How to fetch data from splited qvds

Karim_Khan — Wed, 15 Feb 2017 15:58:45 GMT

If I will go by abc200*.qvd approach it will load 10 years. But currently m looking for only 2 years data.

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 16:05:46 GMT

suppose in a folder you have abc2001, abc2002, abc2003....abc2010. Yes, it will load all but if you have just 2001 and 2002, script will load only 2 yrs of data. so either you can keep all the qvds required in a separate folder. Like if you need to load only abc2009 and abc2010, keep only these qvd in that folder. Another approach would be -

load * from abc2009.qvd (qvd)

Concatenate (a)

load * from abc2010.qvd (qvd)

Re: How to fetch data from splited qvds

Karim_Khan — Wed, 15 Feb 2017 16:18:58 GMT

Thanks Bro,Am clear about this thing but what about below wala code when it will be usefull

Loop code:

sub DoDir (Root)

for each Ext in 'qvd'

for each File in filelist(Root & '\abc*.' & Ext)

Temp:

LOAD Distinct [Shipment ID],

[BL/AWB/PRO],

Container,

[Container Type],

[BL Origin],

[BL Dest],

POL

FROM

[$(File)]

(ooxml, embedded labels);

Next File

Next Ext

for each Dir in dirlist (Root & '\Port Operations*.' & Ext)

call DoDir(Dir)

Next Dir

end sub

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 16:22:41 GMT

It also does the same thing when you have multiple qvds or spreadsheets with different names then you can pull data from those files using this code.

Re: How to fetch data from splited qvds

Anonymous — Wed, 15 Feb 2017 16:24:03 GMT

whenever I get some time i will create a sample qvw for you. Thanks.