Loop

[Channa]

HI

I have 6 QVD files 

i need to create one table in Qlik using those 6 qvd's

Like

tablename , numberofrows, loaddate

table1,100,5/5/2022

table2,200,5/5/2022

table3,300,5/5/2022

any sample script

number of tables can increase 

[vinieme12]

As below

//Iterate through the loaded tables

For t = 0 to NoOfTables() - 1

Tables:

Load TableName($(t)) as Table

, TableNumber(TableName($(t))) as TableNo

, NoOfRows(TableName($(t))) as TableRows

Autogenerate 1

;

Next t;

for Each file in Filelist('somepathhere\*.qvd')
let vReloadTime = FileTime('$(file)')
let vFileName = Mid('$(file)',Index('$(file)','/',SubStringCount('$(file)','/'))+1);

FileInfo:
Load
'$(vFileName)' as Table
,'$(vReloadTime)' as LastReloadtime
Autogenerate 1;
next file ;

Left Join(Tables)

Load *,'dummy' as Dummyfield

Resident FileInfo;

drop field Dummyfield;

Drop table FieldInfo;

[Channa]

Hi @vinieme12 

i am having problem with lib path

it is showing error here in this statement

for Each file in Filelist ([lib://Downloads:Data/1ktMhhmFqRztBM7ykQNIT26vXn_D41JZ_/*.qvd])

it is not accepting lib path in "FileList"

any other way

[vinieme12]

The path should be a string, you are missing single quotes

('lib://Downloads:Data/1ktMhhmFqRztBM7ykQNIT26vXn_D41JZ_/*.qvd')

 Also if this path doesn work test with another path

[Channa]

Hi @vinieme12  i ran this below script i am getting error 

For t = 0 to NoOfTables() - 1

Tables:

Load TableName($(t)) as Table

, TableNumber(TableName($(t))) as TableNo

, NoOfRows(TableName($(t))) as TableRows

Autogenerate 1

;

Next t;

for Each file in Filelist('lib://Downloads:Data\1ktMhhmFqRztBM7ykQNIT26vXn_D41JZ_\*.qvd')
let vReloadTime = FileTime('$(file)');
let vFileName = Mid('$(file)',Index('$(file)','/',SubStringCount('$(file)','/'))+1);

FileInfo:
Load
'$(vFileName)' as Table
,'$(vReloadTime)' as LastReloadtime
Autogenerate 1;
next file ;

Left Join(Tables)

Load *,'dummy' as Dummyfield

Resident FileInfo;

drop field Dummyfield;

Drop table FieldInfo;

I am getting below error 

The following error occurred:

Table 'Tables' not found

The error occurred here:

Left Join(Tables) Load *,'dummy' as Dummyfield Resident FileInfo

[rwunderlich]

A simple script approach can be:

Files:
First 1
LOAD
  FileName() as FileName,
  FileSize() as FileSize,
  QvdTableName(FilePath()) as TableName,
  QvdCreateTime(FilePath()) as CreateTime,
  QvdNoOfRecords(FilePath()) as NoOfRows,
  QvdNoOfFields(FilePath()) as NoOfFields
FROM [lib://Data/CVQS/*.qvd]
(qvd);

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Channa]

Thank you @rwunderlich 

i am trying something like this my loop is not getting repeated

Tables need to be loaded is added to the xls file

TABLES:
LOAD
Table
FROM [lib://Downloads:App/1TsMq4g1_eu4oQroQ6aEbJuJ1oahBapVoacc/Meta.xlsx]
(ooxml, embedded labels, table is Tables) where Load ='Y' ;

for i = 0 to 2;

LET vFileName = Peek('Table',i,'$(TABLES)');

$(vFileName):
First 1
LOAD
FileName() as FileName,
FileSize() as FileSize,
QvdTableName(FilePath()) as TableName,
QvdCreateTime(FilePath()) as CreateTime,
QvdNoOfRecords(FilePath()) as NoOfRows,
QvdNoOfFields(FilePath()) as NoOfFields
From [lib://Downloads:Data/1ktMhhmFaqRzatBMa7ykQNIT26vXn_D41JZ_/SF_01_$(vFileName).qvd](qvd);
NoConcatenate
NEW:
Load * Inline [FileName,FileSize,TableName,CreateTime,NoOfRows,NoOfFields];

Concatenate(NEW)
load * Resident $(vFileName);

drop Table $(vFileName);
Next i;

[rwunderlich]

If I understand correctly, you have a list of QVD files in an xlsx file and want to gather metadata on those QVDs. 

TABLES:
LOAD
Table
FROM [lib://Downloads:App/1TsMq4g1_eu4oQroQ6aEbJuJ1oahBapVoacc/Meta.xlsx]
(ooxml, embedded labels, table is Tables) where Load ='Y' ;

for i = 0 to NoOfRows('TABLES')-1;

LET vFileName = Peek('Table',$(i),TABLES);

NEW:
First 1
LOAD
FileName() as FileName,
FileSize() as FileSize,
QvdTableName(FilePath()) as TableName,
QvdCreateTime(FilePath()) as CreateTime,
QvdNoOfRecords(FilePath()) as NoOfRows,
QvdNoOfFields(FilePath()) as NoOfFields
From [lib://Downloads:Data/1ktMhhmFaqRzatBMa7ykQNIT26vXn_D41JZ_/SF_01_$(vFileName).qvd](qvd);

Next i;

-Rob