Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All
I have following code for getting all excel loaded into qlikview and create qvd my problem is that if one excel is loaded already in qvd
who to skip that excel file for reloading again again is time consuming !
Set vPathXLS='\\inprgcfl06\HOME\Vikas\DataSources';
AsTrk:
Load
*
, FileName() as FileName
From
[$(vPathXLS)\Asset*.xlsx]
(ooxml, embedded labels, table is [$(*)]);
Please advise
Thanks in adv.
Vikas
Set vPathXLS='\\inprgcfl06\HOME\Vikas\DataSources\'; // Your Excel source Path
Set vQVDPath = '\\inprgcfl06\HOME\Vikas\DataSources\QVD\' ; // Your QVD store path
// Load already created QVD with Existing Excel files.
Data:
LOAD Name,
Age,
FileName, // File name created using Filebasename() fucntion while creating qvd first time
Salary,
year
FROM
[$(vQVDPath)Empde.qvd]
(qvd);
// Code to check New excel Files. First 1 load will load only one line so Loading all file names are fast
Check:
first 1 LOAD
FileBaseName() as FileName1
FROM
[$(vPathXLS)Empdet*.xlsx]
(ooxml, embedded labels, table is Sheet1)
where not Exists(FileName,FileBaseName());
if isnull(FileName1) then
Trace "No New Files are available";
else
for i=1 to FieldValueCount('FileName1');
let vFileName = FieldValue('FileName1',$(i));
concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vPathXLS)$(vFileName).xlsx]
(ooxml, embedded labels, table is Sheet1);
next
end if
// Store the newly concatenated data into the QVD again
STORE Data into $(vQVDPath)Empde.qvd;
hello
This code is not considering if i copy new excel file then it should run this file ?
Vikas
Are you getting any error?
Error is nothing but it seems new excel file code is not reading ???
Any Idea I want to close the same
Thanks
Vikas
which code you are using? Can you paste your code ?
Set vPathXLS='\\inprgcfl06\HOME\vikas\DataSources\';
set vQVDFileDir='\\inprgcfl06\HOME\vikas\Qvds\';
Data:
LOAD *
FROM
\\inprgcfl06\HOME\vikas\Qvds\AssetTracking.qvd
(qvd);
Check:
first 1 LOAD
FileBaseName() as FileName1
FROM
[$(vPathXLS)AssetTracking*.xlsx]
(ooxml, embedded labels, table is Sheet1)
where not Exists(FileName,FileBaseName());
if isnull(FileName1) then
Trace "No New Files are available";
else
for i=1 to FieldValueCount('FileName1');
let vFileName = FieldValue('FileName1',$(i));
concatenate(Data)
LOAD *,
FileBaseName() as FileName
FROM
[$(vPathXLS)$(vFileName).xlsx]
(ooxml, embedded labels, table is Sheet1);
next
end if
// Store the newly concatenated data into the QVD again
STORE Data into $(vQVDFileDir)Empde.qvd;
this is my production code but you can apply same with empdet1,2,3 and give me solution you can create empdet4.xls and check whether this code is loading empdet4.xls(new) file ??
Vikas