Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
madhubabum
Creator
Creator

how to skip a Excel Workbook ?

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

1.png

2.png

3.png

7 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

madhubabum
Creator
Creator

Hi Marcus Sommer

Thanks for your reply

It will showing  the following

Error : $(concat)

marcus_sommer
MVP & Luminary
MVP & Luminary

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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

- Marcus

madhubabum
Creator
Creator

Hi Marcus Sommer

Thanks For your replies,

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



Thanks

Madhu

marcus_sommer
MVP & Luminary
MVP & Luminary

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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