topic Re: Creating a new field value based on values from another field in QlikView

Creating a new field value based on values from another field

cliff_clayman — Sat, 16 Nov 2024 03:44:59 GMT

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?

Re: Creating a new field value based on values from another field

asinha1991 — Fri, 26 Apr 2019 06:03:02 GMT

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]

Re: Creating a new field value based on values from another field

Surya — Fri, 26 Apr 2019 06:54:53 GMT

Hi Dear,

Give me your expected output.

Thanks & Regards

Surya

Re: Creating a new field value based on values from another field

cliff_clayman — Fri, 26 Apr 2019 14:33:26 GMT

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

Re: Creating a new field value based on values from another field

Surya — Sat, 27 Apr 2019 04:33:13 GMT

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