topic Re: Multiple Sheets from File in QlikView

Multiple Sheets from File

evansabres — Mon, 15 Feb 2016 19:34:53 GMT

Hello -

My data source is an Excel (.xlsx) file with four (4) sheets, each with a different name and different data on each.

I know how to do a loop script when the data has the same fields on each sheet, but wondering if this is possible with different data.

My sheet names are 'traffic' 'duration' 'referrer' 'content'

Thank you.

Re: Multiple Sheets from File

Oleg_Troyansky — Mon, 15 Feb 2016 19:46:37 GMT

Since the fields are different, the only thing you can do is load them with the generic "*" for all fields and no transformations. Something like this (typing it from memory, so forgive me if the syntax is not exact:)

FOR EACH vSheet IN 'traffic', 'duration', 'referrer', 'content'

$(vSheet):

LOAD

FROM

[... file name ... Table=$(vSheet)]

;

Something along these lines... Copy the exact syntax of the FROM clause from the File Wizard.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Multiple Sheets from File

evansabres — Mon, 15 Feb 2016 19:54:03 GMT

Sorry, I actually couldn't get this to work:

Directory 'Q:\qlkvw\prod\data\digital\HC';

FOR EACH vSheet in 'traffic', 'duration', 'referrer' , 'content';

$(vSheet):

LOAD

*

FROM

(ooxml, embedded labels, Table is $(vSheet);

NEXT

Re: Multiple Sheets from File

sunny_talwar — Mon, 15 Feb 2016 19:57:27 GMT

May be something like this for the attached sample

for Each file in 'A' , 'B'

Table$(file):

LOAD *

FROM

Community_205639.xlsx

(ooxml, embedded labels, table is $(file));

NEXT

Re: Multiple Sheets from File

sunny_talwar — Mon, 15 Feb 2016 19:59:49 GMT

You have a extra semi-colon at the end of FOR statement and you need to remove that:

Re: Multiple Sheets from File

sunny_talwar — Mon, 15 Feb 2016 20:01:53 GMT

In addition you are also missing a closing parenthesis at the end of your load statement: