topic Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders in Connectivity & Data Prep

QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Thu, 31 Oct 2024 15:36:23 GMT

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

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Thu, 31 Oct 2024 16:01:19 GMT

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

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Fri, 01 Nov 2024 14:58:26 GMT

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.

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

p_verkooijen — Fri, 01 Nov 2024 16:12:09 GMT

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

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Fri, 01 Nov 2024 16:22:33 GMT

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

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Fri, 01 Nov 2024 16:49:35 GMT

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>

Re: QlikSense | Sharepoint | Loop through Subdirectories with ListFolders

Oggy172 — Mon, 04 Nov 2024 16:40:29 GMT

@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