Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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.