topic Re: Load Script: Lowercase ALL fieldnames automatically in QlikView

Load Script: Lowercase ALL fieldnames automatically

senior_freshmen — Sat, 16 Nov 2024 20:41:10 GMT

Hello,

I'm loading a excel file into a qlikview document. Unfortunately I can't change the files, which results in the problem that some fieldnames are written in upper case, next week they are in lower case.

Now I would like to automatically lowercase, and then capitalize all header-fields automatically. Is there a way to achieve this?

I can't manually do this like lower(fieldname1), lower(fieldname2) et, because for that I would need to know beforehand

how the fieldnames are written actually, and this would throw me an error because "field not found".

Hope somebody can help me with this

Sincerely

Re: Load Script: Lowercase ALL fieldnames automatically

m_woolf — Wed, 12 Jun 2019 11:49:08 GMT

I suggest not loading the headers.

load
A as Header1,
B as Header2,
etc

Re: Load Script: Lowercase ALL fieldnames automatically

rwunderlich — Wed, 12 Jun 2019 20:44:55 GMT

Most people don't recall (if they ever knew it all) the script FORCE statement. Your solution may be simple:

FORCE Capitalization;
Data:
LOAD *, ....

Force applies to both the fieldname and the field values. You indicated in your original questions that you wanted to lowercase the values and capitalize the fieldnames. If lowercase values is an absolute requirement (you are not just normalizing the values, but must have lowercase) then you can use Force case lower and rename/capitalize the fieldnames in a loop:

FORCE case lower;
Data:
LOAD *, ....

for i = 1 to NoOfFields('Data');
let oldname = FieldName($(i), 'Data');
let newname = Capitalize('$(oldname)');
Rename Field [$(oldname)] to [$(newname)];
next i

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Load Script: Lowercase ALL fieldnames automatically

Vegar — Wed, 12 Jun 2019 21:42:51 GMT

Spot on @rwunderlich, FORCE have been existing out of my radar for many years. When I ran into a similar problem not so long ago. I chose to use the alias function.

Alias dimension1 as Dimension1, DIMENSION1 as Dimension1;

Alias myfield as Myfield, MYFIELD as Myfield;

Data:

Load *

From source;

This approach will interpret both dimension1 and DIMENSION1 as a field called Dimension1; myfield and MYFIELD as Myfield when reading your sources.

Read about alias here: Qlik Sense Help - Alias

Re: Load Script: Lowercase ALL fieldnames automatically

rwunderlich — Wed, 12 Jun 2019 22:17:50 GMT

You could combine the ALIAS solution with the Force case lower instead of the rename loop. I thought the loop might be easier to maintain -- if indeed all fields should be renamed.

ALIAS can be useful when the fieldnames use different spellings as well. https://qlikviewcookbook.com/2018/12/loading-varying-column-names/

-Rob

Re: Load Script: Lowercase ALL fieldnames automatically

senior_freshmen — Thu, 13 Jun 2019 07:02:47 GMT

Hi,

First of all thanks to you(also @rwunderlich for your helpful additions)

But the last hour I struggled with a problem and you may know how to fix it.

It's not working if I load the table with naming the fieldnames like

Alias field1 as Field1; LOAD Field1, Field2, Field3 FROM [sample.xlsx]

I have to load the table with *. Do you know why? Is there a chance to load it without *?

Sincerely

Re: Load Script: Lowercase ALL fieldnames automatically

Vegar — Thu, 13 Jun 2019 10:45:28 GMT

You could do this.

Alias field1 as Field1; TmpSample: LOAD * FROM [sample.xlsx] Sample: NoConcatenate LOAD Field1, Field2, Field3 FROM [sample.xlsx]; Drop TmpSample;

Re: Load Script: Lowercase ALL fieldnames automatically

Akina0929 — Wed, 03 Feb 2021 11:21:34 GMT

Hi Rob,

is there any way to change only script to lower, without data.

Thanks,

Anji