Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Edoardo
Contributor II
Contributor II

Filtering per files' name multiple excel files loaded

Good morning,

I am working on my project related to the loading of multiple excel files, all having the same structures and all placed in the same directory.

The difference between one file to the other, is related to the data of the filename, which is the same of the sheet's name.

 

For example, the today's file is named "R23P10F 180626" and has 1 sheet named "R23P10F 180626"

The yesterday's file is named "R23P10F 180625" and has 1 sheet named "R23P10F 180625" and so on.

 

I have created the script in order to load these multiple files, concatenating all the informations.

My goal is to FILTER per file's name in the app: is it possible? (in the previous example, I should be able to choose a filter that shows "R23P10F 180626" or "R23P10F 180625": if I select the first, the datas in my app would be updated in accordance with my selection)

 

That's the recap of the script used until now to load the multiple files:

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*

FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

1 Solution

Accepted Solutions
Edoardo
Contributor II
Contributor II
Author

Just for info, it was not a problem of file connection.

However, I found the solution by adding at the beginning of the script:

for each file in FileList('lib://DATI/R23P10F *.xlsx')

let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
let v_name_file = filename(); 

trace $(v_sheet);

And then, inside the LOAD function:

LOAD

[...]

filename() as Data_File 

FROM [$(file)]

(ooxml, embedded labels, table is [$(v_sheet)]);

next file;

 

Hope this can help! 

Edoardo

View solution in original post

6 Replies
JordyWegman
Partner
Partner

Hi Edoardo,

If you script is working, it's rather easy. Add your variable to the script and you will now be able to select. See the edited script:

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*,
$(v_sheet) as SheetName
FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

Jordy

Climber

  

Work smarter, not harder
Edoardo
Contributor II
Contributor II
Author

Hi Jordy,

Unfortunately is not working! 

What's about filtering by file's name instead of sheet's name?

This solution would be more suitable for me!...

 

Thanks in advance for your help.

Edoardo

JordyWegman
Partner
Partner

I read it wrong, then you should use the variable called 'file'.

for each file in FileList('lib://DATI/R23P10F *.xlsx')
let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
trace $(v_sheet);

LOAD
*,
$(file) as FileName
FROM [$(file)]
(ooxml, embedded labels, table is [$(v_sheet)]);

Jordy

Climber

Work smarter, not harder
Edoardo
Contributor II
Contributor II
Author

Unfortunately is not working, as this error appears.

What could it be?

Immagine.png

 

JordyWegman
Partner
Partner

This is a connection error. It says it can't find your file. 

Try to open your file manually and see which path it is giving you. You did make the connection right?

Jordy

Climber 

Work smarter, not harder
Edoardo
Contributor II
Contributor II
Author

Just for info, it was not a problem of file connection.

However, I found the solution by adding at the beginning of the script:

for each file in FileList('lib://DATI/R23P10F *.xlsx')

let v_sheet = subfield(SubField('$(file)','/',-1),'.',-2);
let v_name_file = filename(); 

trace $(v_sheet);

And then, inside the LOAD function:

LOAD

[...]

filename() as Data_File 

FROM [$(file)]

(ooxml, embedded labels, table is [$(v_sheet)]);

next file;

 

Hope this can help! 

Edoardo

View solution in original post