Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 [
F1
Netherlands
NETHERLANDS
GermanY
germany
];
F1
GermanY
germany
NETHERLANDS
Netherlands
will become:
Country
Germany
Netherlands