topic Re: How to load all files in different folders. in QlikView

How to load all files in different folders.

neena123 — Tue, 24 Feb 2015 16:49:21 GMT

This is my folder structure. I want to load all files in both folders.

-Distribution:

PFP
- 2014
  - Jan.xlsx
  - Feb.xlsx
  - Mar.xlsx
  - etc...
- 2015
  - Jan.xlsx
  - Feb.xlsx
  - Mar .xlsx
  - etc..

The script I am currently using is grabbing the 2014 data....

FOR EACH vSheet IN 'Alpharetta','Duluth','Chicago','DC','Philly'
Tables:
LOAD Year,
Facility,
Month,
Associate,
Attendance,
Defects,
[In/Out Pool Indicator],
[Payout - Attend],
[Payout - Defects],
Payout
,'$(vSheet)' as Source,
FileName() as FileName
FROM

(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

(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

(ooxml, embedded labels, header is 4 lines, table is [Client Services]);

Re: How to load all files in different folders.

sunny_talwar — Tue, 24 Feb 2015 17:33:18 GMT

Not sure what you question is? Are you looking for ways to improve your script? or is there an error that comes up when you reload?

Best,

Re: How to load all files in different folders.

neena123 — Tue, 24 Feb 2015 17:40:54 GMT

Hi Sunindia,

I am trying to load all files within the folders. Currently my script is working just fine. I just realized I can't keep my script written like that because as year go by more folders will be added. I just can't figure out how to write a script that grabs all the files from the folder that works with the script I have above. This is my folder structure and I am trying to grab all the files with the 2014 and 2015 folders. I appreciate the reply.

-Distribution:

PFP
- 2014
  - Jan.xlsx
  - Feb.xlsx
  - Mar.xlsx
  - etc...
- 2015
  - Jan.xlsx
  - Feb.xlsx
  - Mar .xlsx
  - etc..

Re: How to load all files in different folders.

Anonymous — Tue, 24 Feb 2015 17:41:33 GMT

What happens if you load from

With the * wildcard for the folder ?

Re: How to load all files in different folders.

maxgro — Tue, 24 Feb 2015 17:42:55 GMT

you can add another "for each" for folders

replace absolute path with folder variable

DIRECTORY;

for each folder in '2014', '2015'

// replace and modify with your code

LOAD

rowno() as id,

FROM

[.\$(folder)\*.xlsx]

(ooxml, embedded labels, table is Sheet1);

next;

Re: How to load all files in different folders.

sunny_talwar — Tue, 24 Feb 2015 17:46:13 GMT

Have you thought of using another For loop before your current loop, something like this may be

Let vYear = Year(Today());

For i = 2014 to $(vYear)

NEXT

.... and so on

I am not sure what other delicacies are there, but something like should work?

Best,

Re: How to load all files in different folders.

neena123 — Wed, 25 Feb 2015 14:53:33 GMT

Massimo thanks for the reply! I tried everyone's method but yours was the only one that worked for me however, it created two more tables in my table model. I am still sort of new to qlikview but I do know synthetic keys are bad. Is there a way to get rid of them? I have attached my table model. Thanks for helping me out everyone! I really do appreciate it!!

Re: How to load all files in different folders.

Anonymous — Wed, 25 Feb 2015 15:24:12 GMT

you may want to try force concatenating all the tables...before LOAD ..., type in the keyword Concatenate. you should end up with one table and no synthetic keys.