Only load a table if it exists

[amy_otway]

I'm having trouble with some revenue data I'm trying to import.

It is stored monthly in an .xlsx file with 3 tabs, called 'Table 1', 'Table 2' and 'Table 3'. Table 1 contains the earnings, Table 2 contains the refunds and Table 3 contains corrections from previous months.

The problem I'm having is that the 'Table 3' tab does not exist if there are no historical corrections - so these Excel docs only contain the tabs 'Table 1' and 'Table 2'. How can I tell Qlikview that 'Table 3' is optional and to just move onto the next file if it doesn't exist? The code I'm currently using is:

test:
LOAD 
     [Signup Date], 
     PURGECHAR(Bounty,'$') AS Bounty,
     [N/T], 
     Signup, 
     Track, 
     Referrer,
     IF(LEN(Referrer)<=1,'unkn',SUBFIELD(SUBFIELD(Referrer,'www.',2),'/',1)) AS website
FROM
_INPUT\earnings\*.xlsx
(ooxml, embedded labels, header is 1 lines, table is [Table 1])
WHERE Domain<>NULL();

CONCATENATE(test)
LOAD
     [Signup Date], 
     PURGECHAR(Bounty,'$') AS Bounty,
     [N/T], 
     Signup
FROM
_INPUT\earnings\*.xlsx
(ooxml, embedded labels, header is 1 lines, table is [Table 2])
WHERE Domain<>NULL();


CONCATENATE(test)
LOAD
     [Signup Date], 
     PURGECHAR(Bounty,'$') AS Bounty,
     [N/T], 
     Signup
FROM
_INPUT\earnings\*.xlsx
(ooxml, embedded labels, header is 1 lines, table is [Table 3])
WHERE Domain<>NULL();

However it fails at the first file where there is not a Table 3.

Thanks in advance!

[Frank_Hartmann]

what happens if you add

Set ErrorMode=0;

to the beginning of your script?

[amy_otway]

Hi Frank - thanks for the response.

Putting that at the top of the script does stop it failing at the Excel docs with a missing Table 3, but as the script is carrying out other inputs and calculations, I'm concerned that this will skip over any other errors that I do want flagged.

[Frank_Hartmann · Accepted solution]

then just add

Set ErrorMode = 1;

after your Table3 load  and you will get noticed about any further errors.

this way you are supressing only the known errormessage on table3 (if this table doesnt exits)!

[amy_otway]

Aha, that does the job - thank you!