topic Fill column based on folder name when data reload in QlikView

Fill column based on folder name when data reload

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Fill column based on folder name when data reload

Gysbert_Wassenaar — Thu, 02 Feb 2017 17:45:55 GMT

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

Re: Fill column based on folder name when data reload

anuradhaa — Fri, 03 Feb 2017 03:22:50 GMT

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

Re: Fill column based on folder name when data reload

anuradhaa — Fri, 03 Feb 2017 05:38:59 GMT

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' ;