How to LOAD files from a `for` loop

[qlikdash2000]

I've created a sheet in Qlik Sense with a table. The table lists all Excel files, through Web Connectors, that are located in an Office 365 SharePoint location. All Excel files are similar, based off the same template. On my Qlik Sense sheet I've chosen to list the unique ID for each file, and the path to the file. It looks like this:

ListFiles_UniqueId	ListFiles_id
1cccww47-1702-21be-9b07-8n23858205sy	https://share.companyx.com/sites/ABC201402/.../file01.xlsx')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5	https://share.companyx.com/sites/ABC201402/.../file02.xlsx')
2aag5hsn-26sr-57dd-26hs-6js5shs5hssh	https://share.companyx.com/sites/ABC201402/.../file03.xlsx')
...	...

I've also got a `for` loop in my code. I want this `for` loop to combine all these Excel files, and all the sheets within each Excel file, into one giant table in Qlik. That will be my "master table". I thought the `for` syntax below would achieve this but it doesn't. In my data model viewer I've only got one table, an `Office365Connector_ListFiles` table. I want another table in my data model viewer that is the aggregation of all my Excel files. It would have all the columns from my Excel workbooks (eg "Date", "Store", "Sales", etc.)

What do I do? Here's the code:

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fA...], qvx);

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)'; Office365Connector_ListFiles: UniqueId as ListFiles_UniqueId, id as ListFiles_id FROM [$(vQwcConnectionName)] (URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fA...], qvx);

for each file in filelist('lib://vQwcConnectionName/path/to/directory/*.xlsx')
load * from [$(file)](xlsx);
next file

[pradosh_thakur]

You are correct till here

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

Then load one file from the listed files above using get file an copy paste the code, there would be a parameter field in the url, you have to pass a variable t fetch that. enclose them between like below

FOR Each file_i in FieldValueList('TYPE')

load * from [ your connection name etc](where url is ...... field=$(file_i)&...... token = .....);

next

[qlikdash2000]

Thank you @pradosh_thakur  I did use getFile and the getFile URL is this

http://qlikselfservice:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fRootDir&fileId=v39mrxx7-c90a-4mn9-cc84-73e26k5002t3&appID=&loadAccessToken=owp8x0uwmm 

In that getFile URL I went ahead and replaced:

fileId=v39mrxx7-c90a-4mn9-cc84-73e26k5002t3&

with:

field=$(file_i)&

so that the entire script looks like:

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

FOR Each file_i in FieldValueList('TYPE')
LOAD *
FROM [$(vQwcConnectionName)]
(where url is http://qlikselfservice:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fRootDir&fileId=$(file_i)&appID=&loadAccessToken=owp8x0uwmm);

next

BUT, it doesn't work. I must still be doing something wrong? How do I fix this?