Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

johnmorgan
New Contributor II

Field not found from xlsx doc

I'm pretty new to qlik and trying to debug a load script I inherited.  I have a local data folder with an excel doc (excel 2016, so xslx).  I try to load data from the doc and the first column in the document is not found.   I've tried everything I can think of as far as converting to csv, different versions of excel docs and it seems to always com back to the same thing, anyone have an idea?

Here's the relevant part of the load script:

UserInfo:

LOAD

    UserName,

    Role as UserRole,

    "Group" as UserGroup,

    "Rad Admin" as RadAdmin,

    upper("Group") as REDUCTION

FROM [lib://LocalData (r3_john.morgan)/CMB003AuthorGroups_Tagged.xlsx]

(ooxml, embedded labels, table is Staff);

Here's the Output:

4:11:06 PM

Lines fetched: 79

Field 'UserName' not found

Here's sample of the top of the file:

      

UserNameFamilyNameGivenNameGroupRad AdminRole
systemL000000F0000OTHERNOUser
mmodalL000001F0001OTHERNOUser
shuttleL000009F0009OTHERNOUser

Any help would be awesome.  Thanks in advance

1 Solution

Accepted Solutions
Employee
Employee

Re: Field not found from xlsx doc

This error should indicate that the referred field name does not exist. The reason could vary.

  • Naming is case sensitive. Check that your script field name is spelled exactly as in source.
  • Source field name has blank character that changes the fieldname from "UserName" to "UserName "
  • The load targets incorrect source table
  • XLSX file has header, meaning there are empty lines above the intended header row

For example the attached XLSX file can be loaded with a load statement like this...

LOAD

    UserName,

    FamilyName,

    GivenName,

    "Group",

    "Rad Admin",

    Role

FROM [lib://MyFolderConnection/QC-281312.xlsx]

(ooxml, embedded labels, table is Sheet1);

You can do a wildcard load to see which field names the Qlik engine gets from the source. You can comment the current filed definitions and add a wildcard star in the load statement...

LOAD *

//     UserName,

//     FamilyName,

//     GivenName,

//     "Group",

//     "Rad Admin",

//     Role

FROM [lib://MyFolderConnection/QC-281312.xlsx]

(ooxml, embedded labels, table is Sheet1);

You can evaluate the loaded field names for example through Data Model Viewer.

2017-11-11 10_26_56-Qlik Sense Desktop.png

2 Replies
Employee
Employee

Re: Field not found from xlsx doc

This error should indicate that the referred field name does not exist. The reason could vary.

  • Naming is case sensitive. Check that your script field name is spelled exactly as in source.
  • Source field name has blank character that changes the fieldname from "UserName" to "UserName "
  • The load targets incorrect source table
  • XLSX file has header, meaning there are empty lines above the intended header row

For example the attached XLSX file can be loaded with a load statement like this...

LOAD

    UserName,

    FamilyName,

    GivenName,

    "Group",

    "Rad Admin",

    Role

FROM [lib://MyFolderConnection/QC-281312.xlsx]

(ooxml, embedded labels, table is Sheet1);

You can do a wildcard load to see which field names the Qlik engine gets from the source. You can comment the current filed definitions and add a wildcard star in the load statement...

LOAD *

//     UserName,

//     FamilyName,

//     GivenName,

//     "Group",

//     "Rad Admin",

//     Role

FROM [lib://MyFolderConnection/QC-281312.xlsx]

(ooxml, embedded labels, table is Sheet1);

You can evaluate the loaded field names for example through Data Model Viewer.

2017-11-11 10_26_56-Qlik Sense Desktop.png

johnmorgan
New Contributor II

Re: Field not found from xlsx doc

The sheet name in excel was different than than the load script, I didn't even think to look at that until I saw "table as Sheet1" in your sample above.  Thanks!