topic Load field with changing name due to typo in Excel in QlikView

Load field with changing name due to typo in Excel

CL91 — Sat, 16 Nov 2024 00:10:49 GMT

Hi guys,

I am loading this Excel into Qlikview and due to the fact that 2 versions of an Excel are going around, sometimes a field that I want to load is called Numbers and another time it is called Numers (due to a typo in the Excel).

Now my question is, is it possible to load either Numbers or Numers, without having to go in the script to everytime to make the change manually?

E.g. Sometimes it would look like this (with Numbers):

[Data]:
LOAD Name,
Numbers
FROM
[......xlsx]
(ooxml, embedded labels, table is [Netherlands]);

And sometimes it will look like this (with Numers):

[Data]:
LOAD Name,
Numers
FROM
[......xlsx]
(ooxml, embedded labels, table is [Netherlands]);

Can somebody help me with figuring out how to do this?

Thanks 🙂

Re: Load field with changing name due to typo in Excel

Anil_Babu_Samineni — Fri, 07 Aug 2020 13:56:30 GMT

To ignore script failed. You can manage with Alt() like

[Data]:
LOAD Name,
Alt(Numbers,Numers) as Common
FROM
[......xlsx]
(ooxml, embedded labels, table is [Netherlands]);

Re: Load field with changing name due to typo in Excel

CL91 — Mon, 10 Aug 2020 08:22:49 GMT

Hi Anil,

If I run

[Data]:
LOAD Name,
Alt(Numbers,Numers) as Common
FROM
[......xlsx]
(ooxml, embedded labels, table is [Netherlands]);

I get an error message saying:

Field not found - <Numers> when I load an Excel with Numbers as field name or Field not found - <Numbers> when I load an Excel with Numers as field name.

Re: Load field with changing name due to typo in Excel

Anil_Babu_Samineni — Wed, 12 Aug 2020 08:37:20 GMT

Is that only one field you will get different?

Re: Load field with changing name due to typo in Excel

CL91 — Wed, 12 Aug 2020 08:56:42 GMT

Yes that is the only field.

So only that column header can change between Numers and Numbers.

Re: Load field with changing name due to typo in Excel

hartleyr — Thu, 13 Aug 2020 10:29:10 GMT

You could ignore the header field, and rename them correctly by pulling in the data without it. i.e A as Number, B as ... This way, regardless of the excel file, you would not see a fail. This would be dependant on both excel files having the same column arrangement, but otherwise should resolve the issue