Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)!
what happens if you add
Set ErrorMode=0;
to the beginning of your script?
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.
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)!
Aha, that does the job - thank you!