

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
****Field not found error****
Hi all,
Am trying to load a number of data into Qlikview but I keep getting "Field not found error" .
I have tried loading them individual and it works fine, only when am loading it all at ones then I keep getting Field not found error.
I have tried using NoConcatenate/Concatenate and error still exist.
Scritp below:
LOAD
[Band 3 price],
[Band 4 quantity],
[Band 4 price],
[Band 5 quantity],
[Band 5 price],
[Suspended until],
[Suspended reason],
[Image URL],
Date
FROM
[..\..\DAT_FileData\*******\full_****_904_20_*.xlsx]
(ooxml, embedded labels, table is [full_nhssc_904_20_291018 (1)]);
Could anyone please advice what am I doing wrong.
Thanks
- Tags:
- qlikview_scripting
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
At least in one file a field is missing and/or the fieldnames are different (Qlik is case-sensitive and there is maybe an additionally space or something like that) and/or the sheetnames are different or the filetype is wrong or ...
Wildcard-loads could easily cause such errors especially if you couldn't control the source-files. Therefore it's most better to use a filelist-loop with an explicit concatenating, for example something like this:
dummy: load 'dummy' as dummy autogenerate 1;
for each vFile in filelist('..\..\DAT_FileData\*******\full_****_904_20_*.xlsx')
trace '$(vFile)';
concatenate(dummy)
LOAD
[Band 3 price],
[Band 4 quantity],
[Band 4 price],
[Band 5 quantity],
[Band 5 price],
[Suspended until],
[Suspended reason],
[Image URL],
Date
FROM [$(vFile)] (ooxml, embedded labels, table is [full_nhssc_904_20_291018 (1)]);
next
drop fields dummy;
rename table dummy to XXX;
And if it runs into an error the trace-statement will show where it happens.
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I see you are doing a * load
Is it possible that one of the Excel files don't have one of the field?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your promt respond.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
maybe to diagnose instead of using '*' try a loop and load the files.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
At least in one file a field is missing and/or the fieldnames are different (Qlik is case-sensitive and there is maybe an additionally space or something like that) and/or the sheetnames are different or the filetype is wrong or ...
Wildcard-loads could easily cause such errors especially if you couldn't control the source-files. Therefore it's most better to use a filelist-loop with an explicit concatenating, for example something like this:
dummy: load 'dummy' as dummy autogenerate 1;
for each vFile in filelist('..\..\DAT_FileData\*******\full_****_904_20_*.xlsx')
trace '$(vFile)';
concatenate(dummy)
LOAD
[Band 3 price],
[Band 4 quantity],
[Band 4 price],
[Band 5 quantity],
[Band 5 price],
[Suspended until],
[Suspended reason],
[Image URL],
Date
FROM [$(vFile)] (ooxml, embedded labels, table is [full_nhssc_904_20_291018 (1)]);
next
drop fields dummy;
rename table dummy to XXX;
And if it runs into an error the trace-statement will show where it happens.
- Marcus


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You were definitely right about the filename missing or something wrong with the field name and I couldn’t figured it what it was. I deleted all the files and extract it all again and it work fine. However, I really like your methodology and I would love to understand it better and use it next time I come across issues like this. Cold you please write a simple dummy script that I can follow and use it.
Thank you very much for your time. I really appreciate it.
Solomon


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A filelist() loop is logically quite the same like the classical wildcard-loop because you could use the wildcards to create the needed wildcard-pattern.
The difference is that the filelist-loop is on the outside from the load which meant you could implement various additionally measures, for example checking the filetime() or filesize() or the existing fields or extracting any informations from the filenames/filepath or to enforce a concatenating or to trace any kind of information or to implement a defined error-handling or ... - all things which aren't possible in the classical way.
In my example I chose an explicit concatenating (with the help of a dummy-table) because the automatically concatenating of identical tables will fail if anything in the file-structure is different - it would instead create multiple tables which contain many identical fields and it could create quite heavy synthetic keys which might need a lot of resorces and even lead to a crash.
More background and examples to the filelist-loop could you find here: Loops-in-the-Script and here: QV_QlikView/Scripting/ScriptControlStatements/For Each.
- Marcus


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks very much for this. This is really helpful.
