![](/skins/images/770646D65E4374C9829064958380CFF2/responsive_peak/images/icon_anonymous_message.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
![Specialist](/html/@0E4DF50C0A668F4F5116BEB444E8EE31/rank_icons/Community_Gamification-Ranking-Icons_16x16-Specialist.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
U mean u want to convert ur column in to house upon checking fr tht??
Send data..
Sachin
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
![awhitfield awhitfield](https://community.qlik.com/legacyfs/online/avatars/a150413_untitled.png)
![Partner - Champion](/html/@D9016B49FD846E1AD88CBE9C67131CF6/rank_icons/Community_Gamification-Ranking-Icons_16x16-Partner.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
![](/skins/images/770646D65E4374C9829064958380CFF2/responsive_peak/images/icon_anonymous_message.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
![](/skins/images/770646D65E4374C9829064958380CFF2/responsive_peak/images/icon_anonymous_message.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you
![](/skins/images/770646D65E4374C9829064958380CFF2/responsive_peak/images/icon_anonymous_message.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
![](/skins/images/770646D65E4374C9829064958380CFF2/responsive_peak/images/icon_anonymous_message.png)
- « Previous Replies
-
- 1
- 2
- Next Replies »