****Field not found error****

[solomonadjei]

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

[sunny_talwar]

I see you are doing a * load

Is it possible that one of the Excel files don't have one of the field?

[solomonadjei]

They all have 36 fields, I commented them out one by one and still getting the same error.
Thanks for your promt respond.

[dplr-rn]

does the logs give indication of which file was being loaded?
maybe to diagnose instead of using '*' try a loop and load the files.

[marcus_sommer · Accepted solution]

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

[solomonadjei]

Hi Marcus,
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

[solomonadjei]

Thanks for your support and contribution. I had to delete all the files and extract it all again and it works. I didn’t know where the error was coming from. Once again Thank you very much and have a good Christmas and a New year.

[marcus_sommer]

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

[solomonadjei]

Hi Marcus,
Thanks very much for this. This is really helpful.