Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

sumitjadhav
Contributor II

Creating new fields from existing field.

I have one field in my table i.e, productname for eg;

Productname

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

now , i want to create  three fields for eg:

Type1,Type2,Type3

such as Type1 composed of:a,b,c,d,e,f,i,k

Type2 Composed of m,n,o,p

Type3 composed of h,l,k,x,y,z

How to do it in script.please help.

Thank's in adv.

3 Replies

Re: Creating new fields from existing field.

May be like this:

LOAD Productname,

           If(Match(Productname, 'a', 'b', 'c', 'd', 'e', 'f', 'i', 'k'), 1, 0) as Type1,

           If(Match(Productname, 'm', 'n', 'o', 'p'), 1, 0) as Type2,

           If(Match(Productname, 'h', 'l', 'k', 'x', 'y', 'z'), 1, 0) as Type3

FROM Source

MVP
MVP

Re: Creating new fields from existing field.

another one

Source:

load * inline [

Productname

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

];

Target:

load

  Productname,

  SubStringCount('abcdefih', Productname) as Type1,

  SubStringCount('mnop', Productname) as Type2,

  SubStringCount('hlxyz', Productname) as Type3

Resident Source;

DROP Table Source;

awhitfield
Esteemed Contributor

Re: Creating new fields from existing field.

or another way using a mapping load and Applymap:

Types:
Mapping Load * Inline
[
Productname, Type
a,Type1
b,Type1
c,Type1
d,Type1
e,Type1
f,Type1
g,Type3
h,Type3
i,Type1
j,Type3
k,Type1
l,Type3
m,Type2
n,Type2
o,Type2
p,Type2
]
;


Products:

Load *,
ApplyMap('Types', Productname, 'Unknown') as Types
Inline
[
Productname
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
]
;

Community Browser