topic Re: Append from multiple tabs in QlikView

Append from multiple tabs

Anonymous — Tue, 05 May 2015 14:35:38 GMT

Hi,

gwassenaar‌ jagan‌ swuehl‌ tresesco‌ msolomov‌

I need to append data from multiple tabs in excel into a single file, the field names are the same, attaching the sample file with 3 tabs. Final output should have 9 names with 9 SSOs.

Can anyone guide me on how to do that?

Thanks

Sunny

Re: Append from multiple tabs

robert_mika — Tue, 05 May 2015 14:38:52 GMT

Directory;

LOAD Emp,

SSO

FROM

[Append Test.xlsx]

(ooxml, embedded labels, table is abc);

Directory;

LOAD Emp,

SSO

FROM

[Append Test.xlsx]

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

Directory;

LOAD Emp,

SSO

FROM

[Append Test.xlsx]

(ooxml, embedded labels, table is xyz);

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 14:39:30 GMT

data:

LOAD Emp,

SSO

FROM

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

data:

LOAD Emp,

SSO

FROM

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

data:

LOAD Emp,

SSO

FROM

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

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 14:41:34 GMT

or more elegant :

for Each vSheet in 'abc','xyz','123'

data:

LOAD Emp,

SSO

FROM

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

Re: Append from multiple tabs

Anonymous — Tue, 05 May 2015 14:44:42 GMT

thanks for the prompt reply guys,

a small concern, this was just an example, I have 100 tabs in reality, so will I have to load 100 tables one below the other? or do we have a more smarter solution ?

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 14:46:01 GMT

you have the solution there. You just need to list the tabs in the for each clause

Re: Append from multiple tabs

awhitfield — Tue, 05 May 2015 14:47:17 GMT

LOAD Emp,
SSO
FROM

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

LOAD Emp,
SSO
FROM

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

LOAD Emp,
SSO
FROM

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

Regards Andy

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 14:48:32 GMT

one other solution would be to keep the tab names in a separate excel and read them from there

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 14:50:49 GMT

and this looks like the perfect solution

Loading from multiple Excel files and multiple sheets

Re: Append from multiple tabs

Anonymous — Tue, 05 May 2015 14:53:43 GMT

thanks @ioannis giakoumakis

appreciate your help, whenever you have time, please share this solution "one other solution would be to keep the tab names in a separate excel and read them from there"

just to enhance my knowledge.

thanks again

Re: Append from multiple tabs

giakoum — Tue, 05 May 2015 15:03:05 GMT

it is easier than I thought . The code below works reading directly from your excel provided you have the excel odbc driver installed. Try it :

ODBC CONNECT32 To [Excel Files;DBQ=C:\Users\G96893\Desktop\Append Test.xlsx];

SheetsTemp:

SQL TABLES;

DISCONNECT;

Sheets:

First 1 LOAD

Concat(Chr(39) & PurgeChar(TABLE_NAME, '$''') & Chr(39), ',') as SheetConcatenated

Resident SheetsTemp;

LET vSheetConcatenated = FieldValue('SheetConcatenated', 1);

for Each vSheet in $(vSheetConcatenated)

data:

LOAD Emp,

SSO

FROM

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

DROP Table SheetsTemp;

DROP Table Sheets;