Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
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;
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
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;
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