Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Issue with Explicit labels and No labels while loading an excel into Qlikview document

I'm encountering an issue with loading data from excel into Qlikview. Every month business users uploads an .xlsx file which will be loaded into my data model. Initially when we setup this we used embedded lables and as the business users have full control of the .xlsx file, sometimes they are renaming the columns, which is causing the data model to fail.

So, we switchd to use the explicit lables and started loading the data in the below format

Load
A as Column1,
B as Column2,
C as Column3
FROM [Myfile.xlsx]
(ooxml, explicit labels, table is [Sheet1]);

The above code worked out well for a month, but when business users replaced the .xlsx file next month, our data model started failing with the below error

Error: Field 'A' not found

So, we loaded the excel file again without labels as below and the data model ran successfully.

Load
@1 as Column1,
@2 as Column2,
@3 as Column3
FROM [Myfile.xlsx]
(ooxml, no labels, table is [Sheet1], filters(Remove(Row, Pos(Top, 1))));

The following month business replaced the file again with new data and we encountered the below error

Error: Field '@1' not found

When I reverted back to explicit labels, data model was successful again. And every month I had to alternate between the explicit lables and no labels to avoid the data model failures.

I would greatly appreciate if anyone can suggest a robust and permanent solution for this.


Thanks
Obulreddy

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

There is no technically solution for a conceptual issue. You may load the data multiple times within an if-loop and using ERRORMODE (by an error continue just with the next load-version) and/or you may just use: load * from EXCEL and check afterwards which fields were loaded and in which order to rename them appropriate. But you could never ensure even with hundreds of iterations that there isn't any load-error and even more important that all content is loaded properly within the right fields.

The real solution is to teach the users how they should create/adjust the files/content and to support it with various restrictions / validity checks in the files - for example with a change-protection for the first row and similar stuff.

- Marcus

View solution in original post

1 Reply
MVP & Luminary
MVP & Luminary

There is no technically solution for a conceptual issue. You may load the data multiple times within an if-loop and using ERRORMODE (by an error continue just with the next load-version) and/or you may just use: load * from EXCEL and check afterwards which fields were loaded and in which order to rename them appropriate. But you could never ensure even with hundreds of iterations that there isn't any load-error and even more important that all content is loaded properly within the right fields.

The real solution is to teach the users how they should create/adjust the files/content and to support it with various restrictions / validity checks in the files - for example with a change-protection for the first row and similar stuff.

- Marcus

View solution in original post