Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner - Creator II
Partner - Creator II

Fill column based on folder name when data reload

I have excel sheets belongs to each year (2015,2016,2017). those sheets are stored based on year. so i have three folders.

These sheets doesn't have year .

I want to add coulm to qlikview when reloading based on year.

simply i want to implement for loop kind of thing to get data from all the folders and fill the year based on year.

Is this possible

3 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

You can find a script to loop through folders here: loop through to load all files from a folder and its subfolders? It contains this part:

for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

next SubDirectory

You can use the variable Subdirectory to create the extra year column. Something like this:

LOAD

     *,

     '$(SubDirectory)' as Year

FROM

     ....etc...


talk is cheap, supply exceeds demand
anuradhaa
Partner - Creator II
Partner - Creator II
Author

Hi Thanks for the reply,

Still i have some issues,

it says

Cannot open file 'C:\Users\Dell\Desktop\Tech Stats\Report By Member*.csv' The system cannot find the file specified.

This is the script i use

sub ScanFolder(Data\Years)

          for each SubDirectory in dirlist( Data\Years & '\*' )

                    call ScanFolder(SubDirectory)

[EH_temp]:

LOAD @1 as  [Member],

'$(SubDirectory)' as Year,

FROM

[Report By Member*.csv]

(txt, utf8, no labels, delimiter is ',', msq) where @1 <> 'Grand total' and @1 <> 'Report By Member' ;

next SubDirectory

anuradhaa
Partner - Creator II
Partner - Creator II
Author

I have files in below folder structure

Main folder : C:\Users\Dell\Desktop\Stats\Data\Years

subfolders : 2016 , 2017 , 2015

each folder has n number of files : member****.csv

My qvw file is in the location C:\Users\Dell\Desktop\Stats

I want to load all the files and get new feild call year based on subfolder name when reloading data

i used below script, but it doesn't work.

what's trhe wrong with this script.

for each SubDirectory in dirlist(C:\Users\Dell\Desktop\Stats\Data\Years & '\*' )

   call ScanFolder(C:\Users\Dell\Desktop\Stats\Data\Years)

next SubDirectory

[EH_temp]:

LOAD @1 as  [Report By Member],

@2 as PT,

'$(SubDirectory)' as tempYear,

if(len(trim(TextBetween(@2, '#', ' ')))=0,trim(TextBetween(@2, '#', '')),trim(TextBetween(@2, '#', ' '))) as tId,

trim(TextBetween(@2, '@', ' ')) as productname,

@3 as Date,

@4 as  Hours

FROM

[$(SubDirectory)\Report By Member*.csv]

(txt, utf8, no labels, delimiter is ',', msq) where @1 <> 'Grand total' and @1 <> 'Report By Member' ;