QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

[Oggy172]

Hi all,

New to Qlik (8 days!) and trying to work out a few things. 

Our datastore is sitting on a Sharepoint, and I've created the web connector fine.

I then needed to loop through a folder on the Sharepoint and pull in all .xlsx files in a specific folder. I managed to do this with the below:

LIB CONNECT TO 'Office_365_Sharepoint_Metadata - a@b.com';

LOAD Name as [ListFiles.Name] WHERE WildMatch(Name,'*Shift - *');

SELECT Name
FROM ListFiles
WITH PROPERTIES (
subSite='sites/User/',
folder='/sites/User/Shared Documents/User/Development/Labour Files/SiteName/Qlik Registers',
maxResults=''
);

for each vRemoteFile in FieldValueList('ListFiles.Name')

_DirectHours:
LOAD Line as OrigLine,
applymap('WHLINES',upper(Line), 'Unknown') as Line,
floor(num(date#(mid(filename(),11,10),'DD-MM-YYYY'))) as Date,
     upper(Name) as Name, 
     Upper(Job) as Job,
     if(upper(Name)='AGENCY','Agency','Direct') as Type, 
     Hours,
     'SiteName' as Site,
     filename() as Origin,
     left(filename(),1) as Shift, 
    [Std Role]
FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFile)]
(ooxml, embedded labels, table is Sheet1)
where not isnull([Std Role]) and Hours>0 ;
Next vRemoteFile

Now, this works fine, but I need to adjust to also factor in subdirectories.

eg:

.../SiteName/Qlik Registers/$(vRemoteFolder)/$v(RemoteSubFolder)/$v(RemoteFile)

Can someone shed some light on how I can do a loop of two ListFolders commands ??

Thanks

[Oggy172]

 believe I have sussed it.

for each vRemoteFolder in FieldValueList('ListFolders.Name')
        
        LOAD Name as [ListFolders.Name1];

        SELECT Name
        FROM ListFolders
        WITH PROPERTIES (
        subSite='/sites/User/',
        folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)',
        maxResults=''
        );
        
        for each vRemoteSubFolder in FieldValueList('ListFolders.Name1')

[Oggy172]

Nope, I'm hitting a snag on the reuse of ListFiles.

LIB CONNECT TO 'Office_365_Sharepoint_Metadata - a@b.com';

LOAD Name as [ListFolders.Name];

SELECT Name
FROM ListFolders
WITH PROPERTIES (
subSite='/sites/User/',
folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/',
maxResults=''
);

	for each vRemoteFolder in FieldValueList('ListFolders.Name')
        	
	LOAD Name as [ListFolders.Name1];

	SELECT Name
	FROM ListFolders
	WITH PROPERTIES (
	subSite='/sites/User/',
	folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)',
	maxResults=''
	);
        
		for each vRemoteSubFolder in FieldValueList('ListFolders.Name1')

		LOAD Name as [ListFiles.Name] WHERE WildMatch(Name,'*SHIFT REGISTER*');

		SELECT Name
		FROM ListFiles
		WITH PROPERTIES (
		subSite='sites/User/',
		folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)',
		maxResults=''
		);

			for each vRemoteFile in FieldValueList('ListFiles.Name')

			_DirectHours:
			LOAD Name, 
     		Upper(Job) as Job, 
     		Grade,
     		applymap('WHLINES',upper(Line), 'Unknown') as Line,
     		Line as OrigLine, 
     		Hours, 
     		Reason,
		     if(match(Grade,'Active'or 'active','Agency'),'Agency','Direct') as Type, 
    		// if(Grade='Agency','Agency','Direct') as Type,
      		ApplyMap('ShiftConv',upper(mid(filename(),18,8))) as Date,
     		left(filename(),1) as Shift,
     		'SiteName' as Site,
     		filename() as Origin 
			FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)/$(vRemoteFile)]
			(biff, embedded labels, header is 2 lines, table is [Register$])
			where not isnull(Name) and not match(Reason,'Absent','Sick','L.O.A.','Holiday') and not match(Grade,'UCS') ;

			LOAD Name1 as Name, 
     		Upper(Job1) as Job, 
     		Grade1 as Grade,
     		applymap('WHLINES',upper(Line1), 'Unknown') as Line,
     		Line1 as OrigLine,   
     		Hours2 as Hours, 
     		Reason1 as Reason,
     		if(match(Grade1,'Active','Agency'),'Agency','Direct') as Type,
     		ApplyMap('ShiftConv',upper(mid(filename(),18,8))) as Date,
     		left(filename(),1) as Shift,
     		'SiteName' as Site,
		     filename() as Origin   
			FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)/$(vRemoteFile)]
			(biff, embedded labels, header is 2 lines, table is [Register$])
			where not isnull(Name1) and not match(Reason1,'Absent','Sick','L.O.A.','Holiday') and not match(Grade1,'UCS');

			Next vRemoteFile
            
		Next vRemoteSubFolder

//	Next vRemoteFolder

Stepping through the code, it works fine for the first iteration of ListFiles, and when it hits Next vRemoteSubFolder - it loads in the next subdirectory as expected.

This then loops back to 

LOAD Name as [ListFiles.Name] WHERE WildMatch(Name,'*SHIFT REGISTER*');

		SELECT Name
		FROM ListFiles
		WITH PROPERTIES (
		subSite='sites/User/',
		folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)',
		maxResults=''
		);

However the vRemoteFile remains the same and is not 'calculated' from the new vRemoteSubDirectory.

[p_verkooijen · Accepted solution]

Hi @Oggy172 

Because you do not drop when switching to the next Subfolder

The ListFiles load data will concatenate to the first loaded set.

Give this a name by place ListFiles: before LOAD

ListFiles: LOAD Name as [ListFiles.Name] WHERE WildMatch(Name,'*SHIFT REGISTER*');

		SELECT Name
		FROM ListFiles
		WITH PROPERTIES (
		subSite='sites/User/',
		folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)',
		maxResults=''
		);

Then DROP TABLE ListFiles; after Next vRemoteFile.

			Next vRemoteFile
			DROP TABLE ListFiles;
		Next vRemoteSubFolder

Oh and the same goes for ListFolders

[Oggy172]

Hi,

Yes, I worked that out shortly after. I didnt realise it stored it in the data model when I was creating it, so that's sorted.

Many thanks for your response 

[Oggy172]

One last one, I am trying to exclude something from the ListFolders results

This doesnt seem to work

SELECT Name
FROM ListFolders
WITH PROPERTIES (
subSite='/sites/User/',
folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/SiteName/Qlik Registers/',
maxResults='',
Name='P*');

but this pulls through a folder called AMB (Which I want to exclude)

Or can I use

=if([ListFolders.Name] = 'AMB' , Do nothing, <rest of code> 

?

[Oggy172]

@p_verkooijen I am having strange behaviour in my loop if you could advise? I hope I can explain it well enough.

To explain the sharepoint folder structure - we have 

...../Labour Files/Site/Qlik Registers/Period/

/A Shift/*
/B Shift/*
/C Shift/*

In each shift level folder, there are .xlsx's with the shift register. 

I use ListFolders to loop through the Qlik Registers directory and get the Period (vRemoteFolder)

Then I loop through /$Period/ and utilise ListFolders to get the shift (A, B, C) (vRemoteSubFolder)

Then use ListFiles on this to get the filenames to parse... (vRemoteFile)

However, the folders exist for months that have not happened yet, this is fine, I disable the errormode and then reset it - but something strange happens. Our Fiscal year is Jul-Jun, and so

P1 - Jul

P2 - Aug

P3 - Sep

P4 - Oct 

P5 - Nov

are populated in some capacity

P6 through to P11 are empty folders

P12 has some files (with no data, but theyre synced from somewhere else)

The run order in Qlik is: P4, P1, P9, P6, P11, P8, P12 ......

P4, P1 parse data as expected and ListFiles is dropped.

P9, P6, P11, P8 error but continue due to ErrorMode=0

When it comes back to a folder with files to parse (P12 - Jun), it does:

ListFolders - A/B/C Shift - All ok

ListFiles for B Shift - runs through all files ok

Drop Table ListFiles;

- Error Table not Found

Next vRemoteSubFolder to A Shift, but fails as vRemoteFile hasnt looped.

When I check the Data Model, ListFiles exists in the model.

LIB CONNECT TO 'Office_365_Sharepoint_Metadata - a@b.com';

LOAD Name as [ListFolders.Name];

SELECT Name
FROM ListFolders
WITH PROPERTIES (
subSite='/sites/User/',
folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/Site/Qlik Registers/',
maxResults=''
);


qualify *;

TempTable:
load ListFolders.Name as Name
resident PackingAsset
where ListFolders.Name <> 'AMB';

unqualify *;

	for each vRemoteFolder in FieldValueList('TempTable.Name')
 // if('ListFolders.Name'='AMB'
	LOAD Name as [ListFolders.Name1];

	SELECT Name
	FROM ListFolders
	WITH PROPERTIES (
	subSite='/sites/User/',
	folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/Site/Qlik Registers/$(vRemoteFolder)',
	maxResults=''
	);
        
		for each vRemoteSubFolder in FieldValueList('ListFolders.Name1')
        
   //     LOAD Name as [ListFiles.Name] WHERE 1=2;

		SET ErrorMode=0;
		LOAD Name as [ListFiles.Name] WHERE WildMatch(Name,'*SHIFT REGISTER*');
    

		SELECT Name
		FROM ListFiles
		WITH PROPERTIES (
		subSite='sites/User/',
		folder='/sites/User/Shared Documents/QlikSaaS/Development/Labour Files/Site/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)',
		maxResults=''
		);

			for each vRemoteFile in FieldValueList('ListFiles.Name')
			_DirectHours:
			LOAD Name, 
     		Upper(Job) as Job, 
     		Grade,
     		applymap('WHLINES',upper(Line), 'Unknown') as Line,
     		Line as OrigLine, 
     		Hours, 
     		Reason,
		     if(match(Grade,'Active'or 'active','Agency'),'Agency','Direct') as Type, 
    		// if(Grade='Agency','Agency','Direct') as Type,
      		ApplyMap('ShiftConv',upper(mid(filename(),18,8))) as Date,
     		left(filename(),1) as Shift,
     		'Site' as Site,
     		filename() as Origin 
			FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Development/Labour Files/Site/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)/$(vRemoteFile)]
			(biff, embedded labels, header is 2 lines, table is [Register$])
			where not isnull(Name) and not match(Reason,'Absent','Sick','L.O.A.','Holiday') and not match(Grade,'UCS') ;

			LOAD Name1 as Name, 
     		Upper(Job1) as Job, 
     		Grade1 as Grade,
     		applymap('WHLINES',upper(Line1), 'Unknown') as Line,
     		Line1 as OrigLine,   
     		Hours2 as Hours, 
     		Reason1 as Reason,
     		if(match(Grade1,'Active','Agency'),'Agency','Direct') as Type,
     		ApplyMap('ShiftConv',upper(mid(filename(),18,8))) as Date,
     		left(filename(),1) as Shift,
     		'Site' as Site,
		     filename() as Origin   
			FROM [lib://QVD Data:Office_365_Sharepoint - a@b.com/Shared Documents/QlikSaaS/Development/Labour Files/Site/Qlik Registers/$(vRemoteFolder)/$(vRemoteSubFolder)/$(vRemoteFile)]
			(biff, embedded labels, header is 2 lines, table is [Register$])
			where not isnull(Name1) and not match(Reason1,'Absent','Sick','L.O.A.','Holiday') and not match(Grade1,'UCS');
			Next vRemoteFile


         DROP TABLE ListFiles;
            
		Next vRemoteSubFolder
 		

	set errormode=1;
	Next vRemoteFolder