Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
grajesh_sgp
New Contributor II

Dynamic Load of multiple qvds from a folder

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

Tags (1)
6 Replies
amit_saini
Honored Contributor III

Re: Dynamic Load of multiple qvds from a folder

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

One way could be like this:

TableName:

LOAD *

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

balrajahlawat
Esteemed Contributor

Re: Dynamic Load of multiple qvds from a folder

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

Re: Dynamic Load of multiple qvds from a folder

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;

grajesh_sgp
New Contributor II

Re: Dynamic Load of multiple qvds from a folder

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

Duplicate records meaning synthetic keys or duplicate row of data?

Community Browser