Creating a new field value based on values from another field

[cliff_clayman]

I have a field, Collective Value, that I am trying to use the values for to create a new field, Collective PO.  The values in Collective Value are mixed, some are numeric and some are alphanumeric.  For example, 00, 1, 11, 21, 31, 11CD, 11FP, etc.  There are also blanks in some of the values.  Anything starts with a 1 or is blank I want to assign a value of 'N'.  Anything greater than a 1, I want to assign a 'Y' and anything that starts with a 0, I want to assign 'CO'.  Since the field is not numeric, what is the best method of doing this?

[asinha1991]

simplest way I can think of

create a map

key_map:
Mapping Load * inline[
_key, _Value
0, CO
1, N
blank, N

]

and use this in your load

applymap('key_map',left(replace([Collective Value],'','blank',1)),'Y') as [Collective PO]

[Surya]

Hi Dear,

Give me your expected output.

Thanks & Regards

Surya

[cliff_clayman]

Collective Value	Collective PO
00	CO
1	N
11	N
11CD	N
11FP	N
21	Y
31	Y

[Surya · Accepted solution]

Hope its useful:

new:
load * inline [
sno,name,sal
00,sai,5000
01,nsa,344
03,asd,234
11,sadf,123
12,sad,134
16,gfh,345
15,rty,867
23,sdf,234
,dfg,23445
];

load *,if(WildMatch(sno,'0*'),'CO',
if(WildMatch(sno,'1*'),'N','Y')
) as newlist

resident new;

drop table new;

Thanks & Regards

SP