Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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:
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
@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