Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner - Creator
Partner - Creator

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
sunny_talwar

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
Partner - Creator
Partner - Creator
Author

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
Not applicable

What happens if you load from

        


With the * wildcard for the folder ?

maxgro
MVP
MVP

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

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
Partner - Creator
Partner - Creator
Author

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

Anonymous
Not applicable

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.