Load Script: Lowercase ALL fieldnames automatically

[senior_freshmen]

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

[m_woolf]

I suggest not loading the headers.

load 
    A as Header1,
    B as Header2,
    etc

[rwunderlich]

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

[Vegar · Accepted solution]

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

[rwunderlich]

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

[senior_freshmen]

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

[Vegar]

You could do this.

Alias field1 as Field1;
TmpSample:
LOAD *
FROM 
[sample.xlsx]

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

[Akina0929]

Hi Rob,

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

Thanks,

Anji