topic Field not found error in QlikView

Field not found error

solomonadjei — Thu, 20 Dec 2018 14:51:57 GMT

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

Re: Field not found error

sunny_talwar — Thu, 20 Dec 2018 15:03:26 GMT

I see you are doing a * load

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

Re: Field not found error

solomonadjei — Thu, 20 Dec 2018 15:12:22 GMT

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

Re: Field not found error

dplr-rn — Thu, 20 Dec 2018 15:29:20 GMT

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

Re: Field not found error

marcus_sommer — Thu, 20 Dec 2018 15:54:53 GMT

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)]);

drop fields dummy;
rename table dummy to XXX;

And if it runs into an error the trace-statement will show where it happens.

- Marcus

Re: Field not found error

solomonadjei — Thu, 20 Dec 2018 17:38:47 GMT

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

Re: Field not found error

solomonadjei — Thu, 20 Dec 2018 17:42:42 GMT

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.

Re: Field not found error

marcus_sommer — Fri, 21 Dec 2018 06:57:53 GMT

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

Re: Field not found error

solomonadjei — Fri, 21 Dec 2018 10:25:06 GMT

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

topic ****Field not found error**** in QlikView

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

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

Re: ****Field not found error****

topic Field not found error in QlikView

Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error

Re: Field not found error