Error Loading multiple QVDs using * - Qlik Cloud

[mudi]

Hi, 

I am facing an error loading the data from remote SharePoint folders. It is resolved only when I load them manually and concatenate them. 

The piece of code where fetching the data is.

Thanks 🙂

MY_TABLE:
LOAD

num#(PA) as PA_FY,
KPI as U_KPI
FROM [lib://DEV_SPACE:Office_365_Sharepoint - Team/Analytics/QVDs/KPI/KPI_UPDATED_202500*.qvd]
(qvd)
where num#(PA) = 2025;

Error:

The following error occurred:

Cannot open file: 'lib://DEV_SPACE:Office_365_Sharepoint - Team/Analytics/QVDs/KPI/KPI_UPDATED_202500*.qvd' (Connector error: Failed on attempt 1 to GET. (-2147024894, System.IO.FileNotFoundException, File Not Found.))

Data has not been loaded. Please correct the error and try loading again.

[mpc]

Hi, 

I think you cannot use * syntax with the Sharepoint connector (Office 365 Sharepoint | Qlik Connectors Help see limitation section) 
Maybe you can try to use a loop to load all files corresponding to the desired mask. 

Best

[mudi]

Hi,

Thanks a lot for you help. As I can see from the limitation section, wildcards are not supported. So for the loop, I tried to set up a FOR EACH loop but got the same error - plus - there would be subsequent files to be added later and I want them to be loaded automatically. For example: now there are KPI_UPDATED_2025001 + KPI_UPDATED_202502 ... later would be new ones. Any solutions / suggestions to the code to make it dynamic and get it run correctly? 

[mpc]

Ok then I think you will need to use the Sharepoint Metadata connector: Office 365 SharePoint Metadata | Qlik Connectors Help

Then you can use listFolders and listFiles function, store theses informations in a temp table, and the loop on it with the foreach. Using a variable in the filename should work fine. 

I need several time to provide an exemple if required

Bests

[marcus_sommer · Accepted solution]

Using loops with filelist() is the same as applying the wildcard directly in the file-path. But if your file-names are predictable in any way you could create complete file-paths within a loop, maybe like (simplified):

let start = year(yearstart(today())) * 100 + month(yearstart(today()));
let end = year(today()) * 100 + month(today());

for i = $(start) to $(end)
   let f = 'KPI_UPDATED_' & ($(start) + $(i));
   t: load * from [$(f)] (qvd);
next

[mudi]

Thank you so much Marcus! 

[marksouzacosta]

Hi @mudi ,

As a complement to @marcus_sommer and @mpc , you can use the Sharepoint Metadata.
After setting up the Metadata Connection, open this connection and go to ListFiles.
Enter the Site/Subsite Path and the full path on the Folder, like the picture below:

Next you can load all the Name values to build your loops according to your needs.

Regards,

Mark Costa