Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Concatenate load of Excel sheet that doesn't yet exist.

Good Morning, Got an interesting scenario thats popped up, im running a report that imports vast  amounts of data from Excel into Qlickview.

Usually when you have to load more than 65,536 rows of data, the data will spill into a new tab called sheet2$

im trying to future proof my report so that when sheet 2 is fully populated it will then pull data from sheet 3 so on and so forth

On the XLS file I am importing  these sheets dont yet exist and Qlickview wont let me future proof my report by naming the tabs to be loaded in a future event without me having to go back and modify my report ever month or so.

The code ive used so far.

Source_Data:

LOAD [Contract A/C] as CA,

Balance as Amount

FROM

[*.xls]

(biff, embedded labels, table is Sheet1$);

;

Source_data2:

Concatenate

LOAD @1 as CA,

@3 as Amount

FROM

[*.xls]

(biff, no labels, table is Sheet2$)

;

1 Solution

Accepted Solutions
Not applicable

Concatenate load of Excel sheet that doesn't yet exist.

Hi,

You could add the following statement before  the LOAD statements

SET ErrorMode=0;

This will allow the script editor to ignore errors during script execution.

This way you won't have to go back to the previous script if any errors are encountered.

Hope that helps.

Regards,

-Khaled

3 Replies
Not applicable

Concatenate load of Excel sheet that doesn't yet exist.

Hi,

You could add the following statement before  the LOAD statements

SET ErrorMode=0;

This will allow the script editor to ignore errors during script execution.

This way you won't have to go back to the previous script if any errors are encountered.

Hope that helps.

Regards,

-Khaled

Not applicable

Concatenate load of Excel sheet that doesn't yet exist.

Thankyou very much! Right answer. A BIG However though which is very important. you must type

SET ErrorMode=1; like below otherwise Qlickview will not detect errors made after this line of code is written!

Source_data2:

SET ErrorMode=0;

Concatenate

LOAD @1 as CA,

@3 as Amount

FROM

[*.xls]

(biff, no labels, table is Sheet2$)

;

SET ErrorMode=1;

Not applicable

Concatenate load of Excel sheet that doesn't yet exist.

Hi,

Glad. Yes, should write that definitely.

Regards,

-Khaled