topic Re: convert column name to upper case in QlikView

convert column name to upper case

Sat, 30 May 2015 22:25:55 GMT

Hi all,

Hope all having great weekend.

I'm reading data from multiple xls(100+) files by using a loop.

Problem:

1. case of column names is inconsistent so while concatenating data from each file it creating multiple columns.

Ex: State (from first xls) , state(from second xls) is bought into 2 different columns instead of one due to case inconsistent in xls files.

I tried "Force case upper" but it is converting data to upper case.

Thanks

Ranjith

Re: convert column name to upper case

swuehl — Sat, 30 May 2015 23:01:11 GMT

Maybe have a look at the RENAME method explained in this thread:

Read fields from SQL database with field names lower case only?

Re: convert column name to upper case

fkeuroglian — Sun, 31 May 2015 00:27:17 GMT

Use Capitalize function

good luck

Fernando

Re: convert column name to upper case

Anonymous — Sun, 31 May 2015 04:12:36 GMT

Hi Ranjith

There is no direct method to rename all the fields you need to manually rename those like below

Data:

LOAD

field1 AS FIELD1,

field2 AS FIELD2,

SELECT

field1,

field2

FROM TableName;

OR you can use mapping load like below

Data:

SELECT

field1,field2

FROM TableName;

RenameFields:

Mapping LOAD

field,

Upper(field) AS FieldUpp

INLINE [

field

field1

field2];

Rename Fields using RenameFields;

Hope this helps you.

Re: convert column name to upper case

Sun, 31 May 2015 06:48:03 GMT

Hi Ranjith,

Try like this

Tab:

LOAD dsf,

sgdf,

shd,

dshfg,

dsgf

FROM

C:\Users\hngss\Desktop\Harsha\Delimit.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Sub RenameFields(vTab)

Let vTotal=NoOfFields('$(vTab)');

For vCount=1 to $(vTotal)

Let vName=FieldName($(vCount),$(vTab));

Let vName1=Upper(FieldName($(vCount),$(vTab)));

Rename field $(vName) to $(vName1);

Next;

ENDSUB;

Call RenameFields('Tab');

you can call the RenameFields(lTblename) for each table

Thanks

harsha Nandan

Re: convert column name to upper case

IAMDV — Sun, 31 May 2015 09:23:53 GMT

Hi Ranjith,

Within your loop to extract the .XLS files. You can write crosstable load which can give field names. Convert these field names into upper case whilst loading them into mapping table. Finally you can use this mapping table along with RENAME field syntax.

Hope this helps!

Cheers,

www.QlikShare.com

Re: convert column name to upper case

maxgro — Sun, 31 May 2015 09:50:12 GMT

PFA for an idea (all files in same dir)

if you have a personal edition script is

Directory;

FILE:

load * inline [

file, table

b.xlsx, B_TABLE

a.xlsx, A_TABLE

c.xlsx,C_TABLE

];

// loop and read excel files

for file=0 to NoOfRows('FILE')-1;

let filename=Peek('file', $(file), 'FILE');

let tablename=Peek('table', $(file), 'FILE');

$(tablename):

load * from $(filename)

(ooxml, embedded labels, table is Sheet1);

// loop on table fields to build the "rename as" for every field

let s = '';

for field=1 to NoOfFields('$(tablename)');

let s = '$(s)'

& if(len('$(s)')>0, ',' & chr(10), '')

& '[' & FieldName($(field), '$(tablename)') & ']'

& ' as ' & '[' & upper(FieldName($(field), '$(tablename)')) & '] ';

NEXT field;

// read table using the "rename as"

rename table $(tablename) to TMP;

$(tablename):

NoConcatenate load

$(s)

Resident TMP;

DROP Table TMP;

NEXT file;

Re: convert column name to upper case

Wed, 03 Jun 2015 14:20:25 GMT

Thanks for response, this require multilevel of loading the file but it seems it works for me.

thanks

Re: convert column name to upper case

Wed, 03 Jun 2015 14:21:09 GMT

thanks very much for the response.

Re: convert column name to upper case

Wed, 03 Jun 2015 14:25:16 GMT

Hi harsha,

Thanks for the response.

Already tried that but my table generated from aloop (loaded from multiple excels) will contain fields with different cases, if I use above approach i get error-- Field name should unique error. How ever thanks for the help.

Thanks

Raj

Re: convert column name to upper case

spring101 — Tue, 21 May 2024 13:23:15 GMT

This has been answered well, but there is an easier solution.

force case upper;