Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
CBRZ
New 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
MVP
MVP

Re: Sharepoint headers

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
New Contributor

Re: Sharepoint headers

so the $ is the key to the issue? 

CBRZ
New Contributor

Re: Sharepoint headers

thank you for your response as well!

CBRZ
New Contributor

Re: Sharepoint headers

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

MVP
MVP

Re: Sharepoint headers

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.