Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey There,
I am dynamically loading files into Qlikview.
Audit:
Load W, X, Y, Z
date(DATE#(mid(filename(),7,6), 'YYYYMM'), 'MM-YYYY') as %Date,
mid(filename(),4,2) as %A
From
[file*.csv]
Store Audit into QVDs\Audit.qvd (qvd);
drop table Audit
where file*.csv = file201401, file 201402, file201403 ..... file201501, file201502.csv
i have found that some of the files are empty so I want to iterate through this table, see if data is in the file and load it; if no data, ignore that file and move to the next file
f
//for loop to iterate through tables
for file = 1 to NoOfTables
//get the table name
LET vFile = TableName($(file)-1);
//create variable to store that table name
LET vNames = '$(vFile).qvd';
GL_AUDIT:
LOAD W,
X,
Y,
Z,
date(DATE#(mid(filename(),7,6), 'YYYYMM'), 'MM-YYYY') as %Date,
mid(filename(),4,2) as %A
FROM
[AUDIT*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 3 lines);
STORE $(vFile) into $(vNames)(qvd);
DROP Tables $(vFile);
NEXT file
Any ideas on how to iterate through each file and if the file is empty, move on to the next file?
Thanks
Hi
If the files are empty, do they have zero length, or do they still have header information?
If they have zero length, you can skip the empty files with:
For Each vFile in FileList('folder path')
If FileSize(vFile) > 0 Then
LOAD ...
End If
Next
If they have header information, and you are simply saving the QVD file:
For Each vFile in FileList('folder path')
Test:
LOAD ...
If Alt(NoOfRows('Test'), 0) > 0 Then
STORE Test into ...
End If
Next
If you are saving the results in a consolidated table, just load them. The overhead for the empty files is low, and you need to load them to ensure they are empty unless they are zero length as in the first case.
HTH
Jonathan
The files all have betwen 1-10KB.
Even when there is no data, the file has 1KB but other files might have data and might also be 1KB also.
There would be no headers in the file, the data is completely empty so I want to look into the file, if its empty, move on to the next file.
But it seems that files are not completely empty as the file size is not zero. The simplest way to test if the file has contents, is to attempt to load it - a text file has little overhead, so this may also be the quickest; and it will not require an external tool to analyse the file.
Well ... not technically empty files, that's true.
But for the purpose of the exercise, I need to skip the empty data so going on size doesn't necessarily help.
for (i = 1 to NoOfRows)>0
would that work?
UDIT:
LOAD W,
X,
Y,
Z,
date(DATE#(mid(filename(),7,6), 'YYYYMM'), 'MM-YYYY') as %Date,
mid(filename(),4,2) as %Country
FROM
$(vPath)\AUDIT_*.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 3 lines);
Even if I could use some Error Condition that said
if (W = ""), don't load/skip the CSV, /// if W (field1)is equal to blank, skip the CSV
else if W has a field value, load W, X, Y, Z