Creating new dimension with one value from two values

[sfloberg]

I'm using Field1 as a dimension.
ID can have one or two values in Field1, one letter or one letter and always B, see ID 2.
But now I want Field1 split into a new dimension. In this case - I want a new Field2 with the value A for ID 2 and ignore B from Field1. It should always take the other letter and ignore B. If Field1 only contains one letter (lets say A), Field2 should not do anything.
How I do that?

ID	Field1
1	A
2	A
2	B
3	C
4	A

[Cascader]

my idea is to create a 3rd column in the table:

Table:

Load ID,

Field1,

ID &'-'&Field1 as Temp

then 

Table2:

Load 

if(SubField(Temp, '-', -1)<>'B', Temp) as Field2

resident Table; 

[MayilVahanan]

hi

Try like below

Temp:
LOAD * INLINE [
ID, Field1
1, A
2, A
2, B
3, C
4, A
];
Final:
Load ID, Field1 as Field2 Resident Temp where Field1 <> 'B';

Field 2 doesn't contain "B" value.