Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
senior_freshmen
New Contributor III

Load Script: Lowercase ALL fieldnames automatically

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

 

 

1 Solution

Accepted Solutions
Partner
Partner

Re: Load Script: Lowercase ALL fieldnames automatically

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

Please ekskuse my Norglish and Swenglish typos.
6 Replies
mwoolf
Honored Contributor II

Re: Load Script: Lowercase ALL fieldnames automatically

I suggest not loading the headers.

load 
    A as Header1,
    B as Header2,
    etc

 

MVP & Luminary
MVP & Luminary

Re: Load Script: Lowercase ALL fieldnames automatically

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

Partner
Partner

Re: Load Script: Lowercase ALL fieldnames automatically

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

Please ekskuse my Norglish and Swenglish typos.
MVP & Luminary
MVP & Luminary

Re: Load Script: Lowercase ALL fieldnames automatically

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
New Contributor III

Re: Load Script: Lowercase ALL fieldnames automatically

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

Partner
Partner

Re: Load Script: Lowercase ALL fieldnames automatically

You could do this.

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

Sample:
NoConcatenate LOAD 
Field1, 
Field2, 
Field3
FROM 
[sample.xlsx];
Drop TmpSample;
Please ekskuse my Norglish and Swenglish typos.