creating a dimension from another dimension

[Anonymous]

Hi have the below  following in a field i want to categorize them like

field A:

a

b

c

d

e

f

g

h

i

status:

a,d,e=type1

b,c,f=type2

g,h,i=type3

how to do this

kush141087‌stalwar1‌

[Frank_Hartmann · Accepted solution]

try like this:

Test:

LOAD * INLINE [

    Field

    a

    b

    c

    d

    e

    f

    g

    h

    i

];

Load

Field,

if(Field='a','type1',if(Field='b','type2',if(Field='c','type2',if(Field='d','type1',if(Field='e','type1',if(Field='f','type2',if(Field='g','type3',if(Field='h','type3',if(Field='i','type3'))))))))) as cat

Resident Test;

DROP table Test;

[petter]

If you load this field data from an external source it could be done like this:

LOAD

     A,

     'type' & Pick(Match(A,'a','d','e','b','c','f','g','h','i'),1,1,1,2,2,2,3,3,3) AS Category

FROM

    ......;

With the sample data you showed this should be sufficient.

In a more advanced scenario I would use a different method.