topic Re: Script explanation in QlikView

Script explanation

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

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;

Re: Script explanation

avinashelite — Wed, 02 Mar 2016 13:07:02 GMT

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;

Re: Script explanation

jolivares — Wed, 02 Mar 2016 13:13:58 GMT

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"

Re: Script explanation

sunny_talwar — Wed, 02 Mar 2016 13:14:35 GMT

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

Re: Script explanation

avinashelite — Thu, 03 Mar 2016 05:58:01 GMT

sunindia‌

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