Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI, Thank you for your expertise in advance:
I am trying to use the loop load store method of creating and loading from a qvd.
I have multiple xls in a folder which we have been loading all of the data each time using *.xls however this is becoming more time consuming daily (obviously).
So, i have successfully managed to created the script but can only manage it to check data from 1 of the xls in the given folder
Please see the below and inform if possible a way to loop and read from every file and then append to the qvd.
Thankyou
TAB- SETUP LOOP
Let VStartDate = '42800';
Let VEndDate =today()-1;
TAB - LOOP START
For vCurser = VStartDate to VEndDate
Let vDateToLoad = date(vCurser,'YYYY-MM-DD'); //'2018-02-22'
let vqvdsuffix = text(date(vCurser,'YYYY-MM'));
TAB-LOAD
@1:
LOAD
(data removed)
FROM
\\*************_Dash\Test\test\*.xls
(html, codepage is 1252, embedded labels, table is @1)
Where Date_Imported='$(vDateToLoad)';
TAB- STORE
if NoOfFields ('@1') > 0 then
if not isnull (filetime('\\Scan01\bi\Qlikview\Documents\Development\1LabelData_$(vqvdsuffix).qvd')) Then //Check the file exists
Concatenate (@1)
LOAD
(data removed)
FROM
'\\************\1LabelData_$(vqvdsuffix).qvd'
(qvd)
Where not Exists (Date_Imported);
END IF
Store @1 into '\**************\1LabelData_$(vqvdsuffix).qvd'(qvd);
DROP Table @1;
END IF
Thank you in advance for consideration
Daniel
i suggest you do some research on incremental loading.
3 types of incremental loading:
choose which best suits you then apply.
best wishes.
I have faced same issue before.
I would load all the xls files into Qvd and then Load the data from QVD and then load the latest data from xls file and add it to the existing qvd file.
Like Taylor said, look at some incremental loading details.
Thanks,
Vidya
Some sample code from @amit Saini:
Code to Access all files in directory (subdirectories)
SUB DoDir (Root)
FOR each File in filelist(Root& '\*.xls')
Tab1:
LOAD <<Field Names>>
FROM
[$(File)]
(biff, embedded labels, table is <<Table name>>$);
NEXT File
FOR each Dir in dirlist (Root&'\*')
CALL DoDir(Dir)
NEXT Dir
END SUB
CALL DoDir('Your Directory');
Code to Access All sheets in a Excel work book
Directory;
for a=1 to 3
LOAD employee
FROM
Looping\constructs1.xlsx // this is the excel sheet name
(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a
Next
Code to Access all sheets in a excel work book and convert to QVD
For a=1 to 4
Directory1:
LOAD employee
FROM
Looping\constructs1.xlsx
(ooxml, embedded labels, table is Sheet$(a));
//STORE Directory INTO C:\Users\asaini\Desktop\Looping\Directory$(a).QVD;
Next
STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;
Drop Table Directory1;
Directory;
LOAD employee
FROM
Looping\Directory.QVD
(qvd);
Reference : Dynamic Load of multiple qvds from a folder | Qlik Community
For incremental process into an existing QVD:
//Get the last updated date from you QVD file
LastUpdatedDate:
LOAD
MAX(datecolumn) as MaxDate FROM [qvdfilepath...]/file.qvd(qvd);
// get the first row value
LET vLastUpdatedDate=peek('MaxDatefilter',0,'LastUpdatedDate');
//Filter the data from excel file
Incremental:
Load columns FROM [excelfilepath].xls where datecolumn> $(LastUpdatedDate);
Concatenate
LOAD columns FROM [qvdfilepath...]/file.qvd(qvd);
STORE Incremental into [qvdfilepath...]/file.qvd(qvd);
DROP table Incremental;