5 Replies Latest reply: Mar 10, 2017 6:54 AM by Petter Skjolden RSS

    Sharepoint headers

    Cory Brzak

      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 ?

       

        • Re: Sharepoint headers
          Petter Skjolden

          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

            • Re: Sharepoint headers
              Cory Brzak

              so the $ is the key to the issue? 

              • Re: Sharepoint headers
                Cory Brzak

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

                 

                  • Re: Sharepoint headers
                    Petter Skjolden

                    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.