topic Re: how to skip a Excel Workbook ? in QlikView

how to skip a Excel Workbook ?

madhubabum — Mon, 21 Sep 2015 09:19:28 GMT

Hi All

I have 3 Excel Workbook in D:\ExcelFiles With multiple Sheets as following screen shots;

Particular Sheet Name is same for all Workbooks : Output Tracker.

In 2 nd Work book Row 5 is missing in Output Tracker Sheet.

In such type of Scenarios , I want to Skipp that entire Workbook . How ?

Output : I want to load WorkBook 1 & Work Book 3

Re: how to skip a Excel Workbook ?

marcus_sommer — Mon, 21 Sep 2015 11:02:03 GMT

I think you will need an additionally load-step to check if there is any value in cell A5 available - an quite easy and very flexible way to do such loadings and checkings is to load with filelist and to include the checkings there within a if-loop. Take this as starting point:

for Each file in FileList('D:\ExcelFiles\*.xlsx')

check:

first 5 load A, recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);

let vCheck = peek('Opportunity Type', 4, 'Check');

if len('$(vCheck)') >= 1 then

table:

$(concat)

load *, '$(file)' as filename

from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);

set concat=' concatenate (table) ';

end if

drop tables check; let vCheck = null();

NEXT;

- Marcus

Re: how to skip a Excel Workbook ?

madhubabum — Mon, 21 Sep 2015 11:22:33 GMT

Hi Marcus Sommer

Thanks for your reply

It will showing the following

Error : $(concat)

Re: how to skip a Excel Workbook ?

marcus_sommer — Mon, 21 Sep 2015 11:25:57 GMT

Yes there was something missing:

set concat='';

for Each file in FileList('D:\ExcelFiles\*.xlsx')

check:

first 5 load A, recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);

let vCheck = peek('Opportunity Type', 4, 'Check');

if len('$(vCheck)') >= 1 then

table:

$(concat)

load *, '$(file)' as filename

from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);

set concat=' concatenate (table) ';

end if

drop tables check; let vCheck = null();

NEXT;

- Marcus

Re: how to skip a Excel Workbook ?

marcus_sommer — Mon, 21 Sep 2015 12:15:17 GMT

Yea, there was two small errors with the field- and tablenames which should be written overall equally - but this within the attachment worked.

- Marcus

Re: how to skip a Excel Workbook ?

madhubabum — Tue, 22 Sep 2015 11:39:40 GMT

Hi Marcus Sommer

Thanks For your replies,

How can we skipp a workbook , if field values are not matched ?

Thanks

Madhu

Re: how to skip a Excel Workbook ?

marcus_sommer — Tue, 22 Sep 2015 11:44:18 GMT

The checking within the loop is now extend to the missing data (see attachment):

for Each file in FileList('C:\Temp\*.xlsx')

check:

first 5 load A as [Opportunity Type], recno() as RecNo from $(file) (ooxml, no labels, table is [Output Tracker]);

let vCheck = peek('Opportunity Type', 4, 'check');

if len('$(vCheck)') >= 1 then

let vConcatenate = if(noofrows('table') >= 1, 'Concatenate (table)', 'table:' & chr(10) & 'NoConcatenate');

$(vConcatenate)

load *, 'Data' as Type, '$(file)' as filename, filesize('$(file)') as FileSize, filetime('$(file)') as FileTime

from $(file) (ooxml, embedded labels, header is 5 lines, table is [Output Tracker]);

set concat=' concatenate (table) ';

else

let vConcatenate = if(noofrows('table') >= 1, 'Concatenate (table)', 'table:' & chr(10) & 'NoConcatenate');

$(vConcatenate)

load 'Missing Data' as Type, '$(file)' as filename, filesize('$(file)') as FileSize, filetime('$(file)') as FileTime

Autogenerate 1;

end if

drop tables check; let vCheck = null();

NEXT;

- Marcus

Re: how to skip a Excel Workbook ?

marcus_sommer — Tue, 22 Sep 2015 12:58:16 GMT

I don't understand. Files with data and skipped files are included within the table with filename, filetime and filesize and if you applied such a logic there aren't any script-errors which could be handled and recorded. In general the error-mode will be switched on, continuously queried and then appropriate react and afterwards switched off again - most often implemented within if-loops, see also: ErrorHandling in Qlikview.

- Marcus