Problem Loading Excels stored in two different Sharepoint sites

[Enriquemmesteo]

Hello everybody,

this issue has me going crazy lately cause I can't make it work. Let's see if anybody can help me with this.

Here are the requirements:

• I have to load data from excel files stored in two different Sharepoint sites. Let's call them "Site1" and "Site2".
• I need to load one sheet from each file, which for "Site2" is always called "SheetA" but for "Site2" it is called "SheetA" sometimes and "SheetB" others.
• The structure of the data is essentially the same for all the files.

1. I think to get it working for just one Site using variables, here is the script:

let vQwcConnectionName = 'lib://Share_Point';
// Get Checklist ID list
Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [Site1_URL, qvx);

Table_0:
Load
'dummy' as dummyfield
AutoGenerate (0);

// Load Files

Let ErrorMode = 0; //To make sure it keeps loading if it doesn't 

Set a = 1;

Let b = FieldValueCount('ListFiles_UniqueId');

Do while a <= FieldValueCount('ListFiles_UniqueId')

Let vFileID = FieldValue('ListFiles_UniqueId', a);

FOR EACH vSheet in '[Sheet1]','[Sheet2]';

EXCEL_FILES:
Load

fields

FROM [http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite1%2f&fileId=$(vFileID)&appID=]
(ooxml, no labels, table is $(vSheet));

NEXT vSheet

End If;

Let a = a + 1;

loop

//////END OF SCRIPT VERSION1////

Now, this version works to load all the files from one of the Sharepoints, though I would like to optimize the load. 

Now the Load From statement is for each Site:

1. [http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite1%2f&fileId=$(vFileID)&appID=]
   (ooxml, no labels, table is $(vSheet))
2. [http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite2%2f&fileId=$(vFileID)&appID=]
   (ooxml, no labels, table is $(vSheet))

So there is no reason why I should't be able to change the script before using a variable called vSite. 

Here is that script:

let vQwcConnectionName = 'lib://Share_Point';

// Get Site1 files ID list

Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [Site1_URL], qvx);
//Get Site2 files ID
concatenate
LOAD
UniqueId as ListFiles_UniqueId,

FROM [$(vQwcConnectionName)]
(URL IS [Site2_URL], qvx);

Table_0:
Load
'dummy' as dummyfield
AutoGenerate (0);
Let ErrorMode = 0;

Set a = 1;

Let b = FieldValueCount('ListFiles_UniqueId');

Do while a <= FieldValueCount('ListFiles_UniqueId')
//files from each site have it as prefix
Let vRoute =if(left(FieldValue('ListFiles_Name', a),5)='Site1','Site1%2f&fileId=',
if(left(FieldValue('ListFiles_Name', a),5)='Site2','Site2%2f&fileId='));

Let vFileID = FieldValue('ListFiles_UniqueId', a);
let vROOT ='http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%'&vRoutevFi...';
FOR EACH vSheet in '[Sheet1]','[Sheet2]';
EXCEL_FILES:
Load
fields

FROM [$(vROOT)]
(ooxml, no labels, table is $(vSheet));

NEXT vSheet

End If;

Let a = a + 1;

loop

This gives me INTERNAL SERVER ERROR

[Brett_Bleess]

@Enriquemmesteo Maybe the following Design Blog may be of some help as it is along these lines, but I am not sure:

https://community.qlik.com/t5/Qlik-Design-Blog/Loops-in-the-Script/ba-p/1473543

Regards,
Brett