Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CBRZ
Contributor
Contributor

Sharepoint headers

Hello All,

I created a dashboard based on a link to a sharepoint located on our companies website.  Once I upload the data I go into data load editor, find the web file on the right hand side after loading the data, select data, find the line item @# line item, then check the columns I need for my dashboard.  Once account managers go in and include more line items in the sharepoint and click the "load data" it comes up with an error message as if if can't find the data anymore or the headers have moved.  Is there anyway to lock in the headers so when more line items are included we won't get a load data error ?

5 Replies
petter
Partner - Champion III
Partner - Champion III

You will have to create a more dynamic load script. Referring to what seems to me as a table .... @40 ... must be a small table in the HTML where SharePoint shows a "value".

Your load script might have to look like this:

FOR tableNo=1 TO 100

     SharePointData:

     LOAD

          *

     FROM

           [https://<your-sharepoint-site/...........] (...... table is @$(tableNo) ) ;

NEXT

CBRZ
Contributor
Contributor
Author

so the $ is the key to the issue? 

CBRZ
Contributor
Contributor
Author

thank you for your response as well!

CBRZ
Contributor
Contributor
Author

when I included the $ and attempted the load data it returned this

petter
Partner - Champion III
Partner - Champion III

No it is also a leading and a following ellipsis/paranthese that needs to be there:

$(tableNo)      the ( and ) is important .... it is called a $-sign expansion and it makes Qlik to look for a variable named tableNo in this case and take its value and put in place of the entire expression.

So if the variable tableNo contains the number 247 then @$(tableNo) will turn into  @247, This will happen before it tries to do the load so when the load is going to happen Qlik will try to load the table in HTML named @247.

If it can't find @247 it will give an error. So to get a script that accepts that some tables are not there you need to turn off immediate error handling - that is switch it to silently so it can go on with the loading of tables that might exist and ignore those that doesnt exist. Remember that SharePoint is dynamic so we can't know so much about exactly how many and which of the tables are present from load to load.

We just try a range and read the tables that are there and accept that a number of tables are not...

SET ErrorMode=0;

FOR tableNo=1 TO 100

    SharePointData:

    LOAD

          *

    FROM

          [https://<sharepoint-site/...........] (...... tab @$(tableNo) ) ;

NEXT

SET ErrorMode=1;

Look closely as it is ellipsis around the tableNo variable after the $ !

With ErrorMode=0 we tell Qlik to ignore errors more or less and continue execution and try the next table...

The upper number of 100 is just a wild guess as to how many "tables" there are in a single SharePoint web-page. You have to observe and try it out.