Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

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

Partner
Partner

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

What happens if you load from

        


With the * wildcard for the folder ?

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;

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

Partner
Partner

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

Specialist
Specialist

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.