topic Re: Creating new dimension with one value from two values in App Development

Creating new dimension with one value from two values

sfloberg — Thu, 28 Sep 2023 07:19:02 GMT

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

Re: Creating new dimension with one value from two values

Cascader — Thu, 28 Sep 2023 08:02:43 GMT

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;

Re: Creating new dimension with one value from two values

MayilVahanan — Thu, 28 Sep 2023 08:58:24 GMT

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.