topic Re: Loading file in a loop in QlikView

Loading file in a loop

bobbydave — Fri, 20 Feb 2015 10:58:51 GMT

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

//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,

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

Re: Loading file in a loop

jonathandienst — Fri, 20 Feb 2015 11:25:04 GMT

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

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

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

Re: Loading file in a loop

bobbydave — Fri, 20 Feb 2015 12:14:24 GMT

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.

Re: Loading file in a loop

jonathandienst — Fri, 20 Feb 2015 12:17:30 GMT

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.

Re: Loading file in a loop

bobbydave — Fri, 20 Feb 2015 13:21:42 GMT

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?

Re: Loading file in a loop

bobbydave — Fri, 20 Feb 2015 15:33:40 GMT

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