topic Re: Only load a table if it exists in QlikView

Only load a table if it exists

amy_otway — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Only load a table if it exists

Frank_Hartmann — Thu, 02 Jan 2020 15:38:12 GMT

what happens if you add

Set ErrorMode=0;

to the beginning of your script?

Re: Only load a table if it exists

amy_otway — Thu, 02 Jan 2020 16:02:20 GMT

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.

Re: Only load a table if it exists

Frank_Hartmann — Thu, 02 Jan 2020 16:07:39 GMT

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)!

Re: Only load a table if it exists

amy_otway — Thu, 02 Jan 2020 16:13:08 GMT

Aha, that does the job - thank you!