Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
U mean u want to convert ur column in to house upon checking fr tht??
Send data..
Sachin
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
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;
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
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
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);
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);
Thank you
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