Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

I want to know if there is a way to load this file without separating the sheets up and creating synthetic keys? I have attached the excel file with the different sheets and the field names. Each month a new excel file is created.

I load the sheets: Alpharetta, Duluth, Chicago, DC, and Philadelphia together since the field names are all the same and the header is 3 lines for all of them. 

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(
ooxml, embedded labels, header is 3 lines, table is $(vSheet));

NEXT

The Call Center and Client Services sheets are a little different the field names are called different names and may be positioned in different spots and there is more data needed from these sheets as well:

LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Call Center]);

LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Client Services]);

Any help is appreciated!!

1 Solution

Accepted Solutions

Re: I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

Hi,

Try this way and add FileName() in the load script for identify the File name of the Xls and also as gwassenaar suggest for $(vSheet) as for sheet name then add that also

Script:-

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
Tables:
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
,'$(vSheet)'
as Source,
FileName() as FileName
FROM
[Source*.xlsx]
(
ooxml, embedded labels, header is 3 lines, table is $(vSheet));
NEXT

LET vSheet = Null();
NoConcatenate
Final:
LOAD * Resident Tables;
DROP Table Tables;

LET vSheet = 'Call Center';

Concatenate
LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
,
FileName() as FileName, '$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Call Center]);

LET vSheet = 'Client Services';

Concatenate
LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
,
FileName() as FileName,'$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Client Services]);

Hope this helps

Regards

Anand

View solution in original post

4 Replies
MVP & Luminary
MVP & Luminary

Re: I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

Considering the number of common fields I'd concatenate all the source tables into one table while adding a new field in which the source of the data will be stored:

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD Year,
Facility,
....etc
Payout,

$(vSheet) as Source
FROM
[\\\My Documents\PFP\2014\*.xlsx]
(
ooxml, embedded labels, header is 3 lines, table is $(vSheet));


Perhaps it makes sense to rename the fields Facility, Call Center and Client Services too so the data ends up in one field. And add a new field for the type: Facility, Call Center or Client Services.


talk is cheap, supply exceeds demand
sriharsha96
Contributor II

Re: I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

Hi

Try the below code by making all the data start from row 4(if 4 (ooxml, no labels, header is 3 lines, table is $(vSheet)); ) or 5 ((ooxml, no labels, header is 4 lines, table is $(vSheet));

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
LOAD A as Year,
B as Facility,
C as Month,
D as Associate,
E as Attendance,
F as Defects,
G as [In/Out Pool Indicator],
H as [Payout - Attend],
I as [Payout - Defects],
J as Payout
FROM
[\\\My Documents\PFP\2014\*.xlsx]

(ooxml, no labels, header is 3 lines, table is $(vSheet));


Regards

Harsha


Re: I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

Hi,

Try this way and add FileName() in the load script for identify the File name of the Xls and also as gwassenaar suggest for $(vSheet) as for sheet name then add that also

Script:-

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philadelphia'
Tables:
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
,'$(vSheet)'
as Source,
FileName() as FileName
FROM
[Source*.xlsx]
(
ooxml, embedded labels, header is 3 lines, table is $(vSheet));
NEXT

LET vSheet = Null();
NoConcatenate
Final:
LOAD * Resident Tables;
DROP Table Tables;

LET vSheet = 'Call Center';

Concatenate
LOAD Year,
[Call Center],
Month,
Associate,
Attendance,
Quality,
Product,
[In/Out Pool Indicator1],
[Payout - Attend],
[Payout - Quality],
[Payout - Product],
Payout
,
FileName() as FileName, '$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Call Center]);

LET vSheet = 'Client Services';

Concatenate
LOAD Year,
[Client Services],
Month,
Associate,
Escalations,
Credits,
Defects,
[In/Out Pool Indicator1],
[Payout - Escal],
[Payout - Credits],
[Payout - Defects],
Payout
,
FileName() as FileName,'$(vSheet)' as Source
FROM
[Source\OCT.xlsx]
(
ooxml, embedded labels, header is 4 lines, table is [Client Services]);

Hope this helps

Regards

Anand

View solution in original post

Partner
Partner

Re: I am trying to load an excel file that has more than one sheet with different field names but has the same type of data.

I am just a beginner and this has helped me so much thank you!