topic Re: Normalizing Upper and Lower characters of fields after loading data in QlikView

Normalizing Upper and Lower characters of fields after loading data

nls — Fri, 03 Oct 2014 08:07:05 GMT

Hi,

I'm loading "County" from an excel file but the fields are either in UPPER or lower case (e.g. GREECE and greece) so they show as different countries in my app.

How do I normalize the fields after I've loaded data, without having to fix the source data?

thanks/n

Re: Normalizing Upper and Lower characters of fields after loading data

rubenmarin — Fri, 03 Oct 2014 08:12:27 GMT

Hi Nick, there are some strings functions to normalize the values like upper, lower and capitalize:

upper( 'abcD' ) returns 'ABCD'. --> all upper case

lower( 'abcD' ) returns 'abcd'. --> all lower case

capitalize ('my little pony') returns 'My Little Pony' --> first letter of eah word is upper case, the other lower case

Re: Normalizing Upper and Lower characters of fields after loading data

Michiel_QV_Fan — Fri, 03 Oct 2014 08:17:09 GMT

First set all data to lower case and then Capitalize or Upper case:

Capitalize(lower(Country field))

Load *,

Capitalize(lower(F1)) as Country;

LOAD * INLINE [

Netherlands

NETHERLANDS

GermanY

germany

];

GermanY

germany

NETHERLANDS

Netherlands

will become:

Country

Germany

Netherlands