Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to identify lower/upper case/special characters with in a field

ex

House- correct format

HOUSE-Upper case

house-Lower case

#$house$- Special character

need help in scripting on

how to check a field whether

it is correctformat/upper case/

lower case/special character

1 Solution

Accepted Solutions
sunny_talwar

Added error message field

Table:

LOAD *,

  If(Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0, 'Special Character',

    If(Field = Capitalize(Field), 'Correct Format',

          If(Field = Upper(Field), 'Upper Case',

              If(Field = Lower(Field), 'Lower Case')))) as [Error Message];

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

]

WHERE Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0 or Field = Upper(Field) or Field = Lower(Field);

Capture.PNG

View solution in original post

18 Replies
sdmech81
Specialist
Specialist

U mean u want to convert ur column in to house upon checking fr tht??

Send data..

Sachin

sunny_talwar

May be this:

If(Len(Trim(KeepChar(FieldName, '!@#$%^&*()'))) > 0, 'Special Character',

     If(Field = Capitalized(Field), 'Correct Format',

          If(Field = Upper(Field), 'Upper Case',

               If(Field = Lower(Field), 'Lower Case')))) as Flag

Chanty4u
MVP
MVP

Hi,

Am not sure but this will give you

a:

LOAD * INLINE [

    Data

    House- correct format

    HOUSE-Upper case

    house-Lower case

    #$house$- Special character

    need help in scripting on

    how to check a field whether

    it is correctformat/upper case/

    lower case/special character

];

Result:

LOAD

KeepChar(Data,'abcdefghijklmnopqrstuvwxyz') as lower,

KeepChar(Data,'ABCDEFGHIJKLMNOPQRSTUVWXYZ') AS Uupper,

KeepChar(Data,'!@#$%^&*()') as case

Resident a;

upper.PNG

awhitfield
Partner - Champion
Partner - Champion

Hi raga,

1. to set the case, use Capitalize, e.g.

Capitalize(data) as data

2. to remove the unwanted characters, use purge char / keep

See the following: PurgeChar and KeepChar Functions

Andy

Anonymous
Not applicable
Author

thanks all for your replies.

the requirment i have is i need to capture

rows only which are in incorrect format.

no name

1 Apple

2 CAT

3 $Dog

4 hen

output should load only 2,3,4 rows

as all of these are not in standard format.

i also need the error message for those

2,3,4 rows as

no name remarks

2 CAT complete nam in upper cas

3 $Dog has special characters

4 hen complete name in lower case

plz help.

thanks

sunny_talwar

Try a where statement like this:

Table:

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

]

WHERE Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0 or Field = Upper(Field) or Field = Lower(Field);

Capture.PNG

sunny_talwar

Added error message field

Table:

LOAD *,

  If(Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0, 'Special Character',

    If(Field = Capitalize(Field), 'Correct Format',

          If(Field = Upper(Field), 'Upper Case',

              If(Field = Lower(Field), 'Lower Case')))) as [Error Message];

LOAD * Inline [

Field

Apple

CAT

$Dog

hen

]

WHERE Len(Trim(KeepChar(Field, '!@#$%^&*()'))) > 0 or Field = Upper(Field) or Field = Lower(Field);

Capture.PNG

Anonymous
Not applicable
Author

Thank you

Anonymous
Not applicable
Author

Hi Sunny,

How about for this case. 5,6 rows

no name

1 Apple

2 CAT

3 $Dog

4 hen

5 GOAt

6 bOaT

no name remarks

2 CAT complete nam in upper cas

3 $Dog has special characters

4 hen complete name in lower case

5 GOAt Irregular case format

6 bOaT Irregular case format