topic Re: Dynamic Load of multiple qvds from a folder in QlikView

Dynamic Load of multiple qvds from a folder

grajmca_sgp123 — Mon, 30 Nov 2015 11:44:53 GMT

Hi All,

Need help with this scenario from scripting gurus?

I have a situation where I have to load and concatenate all qvds dynamically.

This is my folder structure

MainFolder

SubFolderr

Month_31-Aug-2015.qvd,

Month_30-Sep-2015.qvd,

Month_31-OCT-2015.qvd

Month_30-Nov-2015.qvd,

Month_31-DEC-2015.qvd

How to load all the files dynamically if i give only "MainFolder" Name.

Thanks in advance

Regards

Re: Dynamic Load of multiple qvds from a folder

amit_saini — Mon, 30 Nov 2015 11:46:53 GMT

Rajesh,

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;
for a=1 to 3
LOAD employee
FROM
Looping\constructs1.xlsx // this is the excel sheet name
(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a
Next

Code to Access all sheets in a excel work book and convert to QVD

For a=1 to 4
Directory1:
LOAD employee
FROM
Looping\constructs1.xlsx
(ooxml, embedded labels, table is Sheet$(a));
//STORE Directory INTO C:\Users\asaini\Desktop\Looping\Directory$(a).QVD;
Next
STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;
Drop Table Directory1;
Directory;
LOAD employee
FROM
Looping\Directory.QVD
(qvd);

Thanks,
AS

Re: Dynamic Load of multiple qvds from a folder

sunny_talwar — Mon, 30 Nov 2015 11:46:54 GMT

One way could be like this:

TableName:

LOAD *

FROM MainFolder\SubFolder\Month_*.qvd (qvd);

Re: Dynamic Load of multiple qvds from a folder

Anonymous — Mon, 30 Nov 2015 11:50:51 GMT

If all tables are with same fields and structure then try what Sunny suggested you

Re: Dynamic Load of multiple qvds from a folder

Kushal_Chawda — Mon, 30 Nov 2015 12:11:23 GMT

Sometimes it may happen that all files don't have same number of fields so it is better to use for loop like below

//Read all file name

Files:

First 1 LOAD FileBaseName() as FileName

FROM

[FolderPath\*.qvd]

(qvd);

Table:

LOAD * Inline [

Junk ];

// loop to load all files

for i=1 to FieldValueCount('FileName');

let vFileName = FieldValue('FileName',$(i));

Concatenate(Table)

LOAD *,

FileBaseName() as FileName

FROM

[FolderPath\$(vFileName).qvd]

(qvd);

next

DROP Table Files;

DROP Field Junk;

Re: Dynamic Load of multiple qvds from a folder

grajmca_sgp123 — Tue, 01 Dec 2015 08:03:17 GMT

yes my tables have same fields and structure only , but getting huge duplicate records with this solution.

Re: Dynamic Load of multiple qvds from a folder

sunny_talwar — Tue, 01 Dec 2015 10:57:52 GMT

Duplicate records meaning synthetic keys or duplicate row of data?