topic Problem loading multiple Excel worksheets in QlikView

Problem loading multiple Excel worksheets

Fri, 24 Feb 2012 21:30:09 GMT

I am trying to load mutiple worksheets from the same Excel spreadsheets. I can see that the lines are being fetched properly however, the first worksheet links correctly and subsequent ones do not. If I comment out the top spreadsheet then the next spreadsheet works fine but not subsequent ones. Basically, only the first load works all others do not. I have no idea what to try. I have copied an example below. Thanks for any help!

Oh, on QV v. 10 and Excel 2010. myTable contains a corresponding %Key1

LEFT JOIN (myTable)

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet1]);

LEFT JOIN (myTable)

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet2]);

LEFT JOIN (myTable)

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet3]);

Problem loading multiple Excel worksheets

Fri, 24 Feb 2012 22:04:50 GMT

Are you doing the left join for some reason?

Why don't you just make a table concatenating all the excel files...

Excel:

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet1]);

Concatenate

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet2]);

Concatenate

LOAD

field1 AS [%Key1],

field2,

field3

FROM

(ooxml, embedded labels, table is [worksheet3]);

Problem loading multiple Excel worksheets

Fri, 24 Feb 2012 22:53:54 GMT

Concatenate works ( I tried something similar just after I wrote this, thanks!), but still curious why the left join would not. Either should be a viable option.

What I did:

FOR EACH vSheet IN 'worksheet1', 'worksheet2', 'worksheet3'

Excel:

LOAD

field1 AS [%Key1],

field2,

field3

FROM

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

NEXT;

Problem loading multiple Excel worksheets

Fri, 24 Feb 2012 23:07:59 GMT

Maybe because when you do the 1st join, your result table has field2 and field3.

The next join, doesn't add any fields to the result table.

For instance

my table has %Key1 with 1

Sheet1 has

field1 field2 field3

1 1 1

Sheet 2 has

field1 field2 field3

1 2 2

After first join, result is

1 1 1

When you make the 2nd join, left join searches for the combination 1 1 1 can't find any matches.

And even if find a match, won't be adding any lines.

Problem loading multiple Excel worksheets

mdmukramali — Sat, 25 Feb 2012 09:10:43 GMT

Dear,

if you want to load multiple worksheets from the same excel sheet then you can use for loop syntax

FOR loop=1 to 3

LOAD Sno as Snumber,

Sname,

Amount

FROM

[MULTIPLE SHEETS.xlsx]

(ooxml, embedded labels, table is Sheet$(loop));

NEXT;

hope this one will help you.

Problem loading multiple Excel worksheets

Anonymous — Sun, 26 Feb 2012 01:46:19 GMT

Adding to the above from Frederico:
Unlike SQL, where you specify explicitly on which fields to join, in QV it is different - join is happening on ALL common fields, in this case on field1(%Key1), field2, and field3.