How to load all files in different folders. - Qlik Community

neena123 · ‎2015-02-24

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]);

sunny_talwar · ‎2015-02-24

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,

S

neena123 · ‎2015-02-24

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..

Anonymous · ‎2015-02-24

What happens if you load from

With the * wildcard for the folder ?

maxgro · ‎2015-02-24

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;

sunny_talwar · ‎2015-02-24

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)

     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

.... and so on

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

Best,

S

neena123 · ‎2015-02-25

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!!

Anonymous · ‎2015-02-25

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.