Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

Script explanation

Hi,

Actually need an detailed explanation for the below script like how each script line works,



LET vFilePath = 'D:\Qlikview_dev\Qlikview__Template\Developers\nar\budget';

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

FOR EACH file in FileList('$(vFilePath)\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:

SQLtables;

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

4 Replies
avinashelite

This script is used to load Multiple Excel files dynamically from a folder with the file names and sheet names ...

// initialising the path where the excels are stored

LET vFilePath = 'D:\Qlikview_dev\Qlikview__Template\Developers\nar\budget';

// dummy table , actually this not required

Data:

LOAD

'' AS Dummy

AutoGenerate (0);


// to get the all the files list in the folder

FOR EACH file in FileList('$(vFilePath)\*.xlsx');


// connecting for the files through the ODBC connection , this has advantages 

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

SheetNames:// table name

SQLtables;// its a SQL function

DISCONNECT;

// to read all the sheets in the excel

FOR index = 0 to NoOfRows('SheetNames')-1

LET sheetNamez = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;


jolivares
Specialist
Specialist

This script in a few words is reading multiples excel files with multiples or not sheets inside and create a table named "Tab"

Step by Step:

1.- Assign a file path to a variable : "vFilePath"

2.- Create a table named "Data" with a field Dummy

3.- Make a For loop to iterate over each file

4.- Coonect to a file that is reading

5.- Assign each sheet name

6.- Make a For loop to read each sheet in every file

7.- Assign each sheet name to a variable "sheeNamez"

8.- Read each sheet  and concatenate to the table "Data"

9.- Drop the field Dummy from the table "Data"

sunny_talwar

Avinash R wrote:

// dummy table , actually this not required

Data:

LOAD

'' AS Dummy

AutoGenerate (0);


Avinash‌ I am going to disagree with you on this, I think the dummy table is required unless you add a variable to below statement instead of Concatenate(Data)

Concatenate(Data)

Tab:

Load *,

  Month( GltPostingDate) as GltMonth,

    Year( GltPostingDate) as GltYear,

  GltAcctUnit&'-'&GltAccount&'-'&GltSubAccount as SheetName,

  '$(sheetNamez)' as Sheet_names

From $(file)(ooxml, embedded labels, table is [$(sheetNamez)]);

NEXT index

DROP TABLE SheetNames;

NEXT

avinashelite

sunindia

Since the table structure is same Qlikview will automatically concatenate to the TAB table rite??  why do we need the Data table