Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

neena123
New Contributor II

How to load all files in different folders.

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

7 Replies
MVP
MVP

Re: How to load all files in different folders.

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
New Contributor II

Re: How to load all files in different folders.

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.

What happens if you load from

        


With the * wildcard for the folder ?

MVP
MVP

Re: How to load all files in different folders.

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;

MVP
MVP

Re: How to load all files in different folders.

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

NEXT

.... and so on

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

Best,

S

neena123
New Contributor II

Re: How to load all files in different folders.

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

tables.PNG

Highlighted
jsaradhi
Valued Contributor

Re: How to load all files in different folders.

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.