15 Replies Latest reply: Mar 28, 2017 2:43 AM by F Laurentiu RSS

    Field not Found

    Ajinkya Bhonsle

      Hi Guys,

       

      I am very new to QlikSense. I am trying to run a script which pulls the data from SharePoint.The field does exist in the exel file. And I have also checked for the name of the field to be case sensitive or not.

       

        I am getting the below error:

       

      Date << Last Update Date
      Lines fetched: 1

      The following error occurred:
      Field not found - <Team>

      The error occurred here:
      Checkbook:
      LOAD
          Team,
          Department,
          "Year",
          "Group",
          Quarter,
          "Budget Line",
          "Budget Plan $",
          "Budget Sub-Category",
          "Budget Category",
          "Funnel Stage",
          "Name/ Description",
          "Parent Campaign",
          "Campaign/ Initiative",
          "Tactic/Activity",
          "Finance Tactic Group" as "Tactic Group",
          "Forecast Amount",
          Committed,
          JMF,
          Vendor,
          If(Match([Tactic/Activity],'Advertising - Digital','Collateral - e-book','Content Syndication','E-mail','Event - Online','Online Syndication','Paid Search','SEO','Social Media Promotion','Webinar')=0,'Other','Digital') AS DigitalMarketingFlag
      FROM [lib://SharePoint - Marketing Finance/Shared Documents\2017 Checkbook Registers\Global\GLOBAL checkbook.xlsx]
      (ooxml, embedded labels, table is [Roll Up]) where not [Team]=0

        • Re: Field not Found
          Onno van Knotsenburg

          Looks like your source does not have a "Team" field.

          • Re: Field not Found
            Ihor Ostapchuk

            Ajinkya Bhonsle, the best would be a checkbook.xlsx file that illustrates the field "team".

            • Re: Field not Found
              Dave Riley

              Try running the script without the Where clause and as a LOAD * to see which fields are available.

               

              The error looks to have the field Team tagged in xml format ('<Team>') which may suggest there is no data populated in that field and therefore doesn't appear in the xml structure - I've found this happens when connecting to SharePoint data sources in other ways, but I could be wrong here.

              • Re: Field not Found
                F Laurentiu

                One of the pitfalls for most people new to the scripting is the case sensitivity. Even more experienced Qlik users tend to make a typo with regard to this.
                Camel, camel, CaMel are all different to Qlik and therefore might cause the behavior. But as you have stated this is not the case..

                 

                The only option is that the Team field does not exist in the sheet named [Roll Up] in the selected workbook.
                I agree with Dave Riley that the best way to go forward is to load the XLSX from sharepoint without a where clause and have a look at the available fields.

                Could it be specific naming or maybe a hidden space in the field name?

                  • Re: Field not Found
                    Ajinkya Bhonsle

                    Hi Ferry,

                     

                    Thank you for your comments. I did check the data in the source file. It does exist.

                    Qlik Screenshot.PNG

                    in xlsx:

                    Qlik Screenshot1.PNG

                    However the data loads properly without errors when I created a new app.

                      • Re: Field not Found
                        F Laurentiu

                        You can add some tracing in between statements and use the debug loading to check if the prior statements do not cause an issue: 

                        TRACE # Some text to indicate script position

                         

                        If the statement without a where clause still gives an error, it might also come from the field list. Try to load all fields with the wildcard * and see if it works.
                        If it does, try commenting out the entire load statement and try to create a new one using the data load wizard. Then compare the statements and check what is different.

                          • Re: Field not Found
                            Ajinkya Bhonsle

                            Hi Ferry,

                            I tried it without the names and without the where clause it gives me the below error:

                            Date << Last Update Date Lines fetched: 1

                            The following error occurred:

                            Error: File Extdata.cpp, Line 2391

                            The error occurred here:

                            Checkbook: LOAD * FROM [lib://SharePoint - Marketing Finance/Shared Documents/2017 Checkbook Registers/Global/GLOBAL checkbook.xlsx] (ooxml, embedded labels, table is [Roll Up]).

                             

                            To give you a background: The "Global Checkbook" xlsx file was under a shared folder(Marketing Files) initially. But now since the SharePoint connection works I tried to pull the the same Global Checkbook file from SharePoint and hence I happen to come across this error.

                            Fyi:

                            Dashboard failed to refresh since the connection is to SharePoint rather than to the shared folder.

                             

                            I don't get it its the same file. But the only difference is now the connection is to SharePoint and not to the shared folder.

                             

                            Please help!.

                              • Re: Field not Found
                                F Laurentiu

                                I have only limited experience with the Sharepoint environment.
                                The error points in the direction of either security on the sharepoint file/path or a path that it can't find in the sharepoint Lib definition.
                                It could be that you already pointed the Lib to a certain folder, in this case 'Marketing Files' and in the file path you again mention the folder. Which will result in something like this:

                                Sharepoint:/Marketing Files/Marketing Files/GLOBAL checkbook copy.xlsx

                                 

                                Could this be the case?

                                 

                                Regards,

                                Ferry

                          • Re: Field not Found
                            Ajinkya Bhonsle

                            Hi Ferry and Everyone,

                             

                            It was a very silly mistake on my end. The Table [Roll Up] had a "-" between the Roll and Up.

                            The actual table was [Roll-Up] and it was causing to not finding the field <Team>.

                             

                            Thank you everyone to troubleshoot the issue. Appreciate your help very much.

                          • Re: Field not Found
                            Sasidhar Parupudi

                            First check if you missed a ';' after the where condition where not [Team]=0

                             

                            Second

                            Does it load any data if you remove the where condition

                             

                            where not [Team]=0

                             

                             

                            third

                            Try setting the Team to a known value

                            where not [Team]='My Team';

                              • Re: Field not Found
                                Ajinkya Bhonsle

                                Hi Sasidhar,

                                 

                                I checked for ';' and I tried to load the data without the where condition. It still gives me the same error.

                                Also the field does exist in the source data.

                                 

                                Qlik Screenshot.PNG

                                  • Re: Field not Found
                                    Ihor Ostapchuk

                                    would you try load without names o field.

                                    Just

                                     

                                    Load

                                       *

                                    FROM [lib://SharePoint - Marketing Finance/Shared Documents\2017 Checkbook Registers\Global\GLOBAL checkbook.xlsx]

                                    ;



                                      • Re: Field not Found
                                        Ajinkya Bhonsle

                                        Hi Ihor,

                                         

                                        I tried it without the names of the field it gives me the below error:

                                         

                                        Date << Last Update Date Lines fetched: 1

                                         

                                        The following error occurred:

                                         

                                        Error: File Extdata.cpp, Line 2391

                                         

                                        The error occurred here:

                                         

                                        Checkbook: LOAD * FROM [lib://SharePoint - Marketing Finance/Shared Documents/2017 Checkbook Registers/Global/GLOBAL checkbook.xlsx] (ooxml, embedded labels, table is [Roll Up]).

                                         

                                        To give you a background: The "Global Checkbook" xlsx file was under a shared folder initially. But now since the SharePoint connection works I tried to pull the Global Checkbook file from SharePoint and hence I happen to come across this error.

                                      • Re: Field not Found
                                        Arnaldo Sandoval

                                        Hi Ajinkya.

                                         

                                        Try these troubleshooting suggestions:

                                         

                                        • Exclude the Team column from the LOAD and WHERE filter: if it works some special characters are part of its name.

                                         

                                        or

                                         

                                        • Rename Team to something else in your Excel file and script (NOTE: Make a copy of the Excel file and try this test with the copy).

                                         

                                        or

                                         

                                        • You are mixing Team (in the LOAD) with [Team] in the WHERE, try enclosing Team in double quotes in the LOAD, like this "Team".

                                         

                                        Hope this helps,