Create dimension from two dimensions

[danielrogra]

Hi All

I have two dimensions as follows:

Dimension1	Dimension2
A	A.1
A	A.2
B	B.1
B	B.2

and I want to create the following dimensions

New dimensions	Total
A	sum where dimensions2 in (A.1,A.2)
B	sum where dimensions2 in (B.1,B.2)
C	sum where dimensions2 in (A.2,B.2)

How can I create that?

Thanks in advance

Regards

[joseph_eftamand]

Do you need to create the dimension with those parameters or perform the calculation defined by Dimension 1 = A and Dimension 2 = A.1?

In that case it could just be set analysis: Sum({<Dimension1 = {'A'}, Dimension2 = {'A.1'}>}Metric)

                                                                 Sum({<Dimension1 = {'C'}, Dimension2 = {'A.1', 'B.2'}>}Metric)

To create a new dimension you can just use a conditional statement in the script:

if(Dimension2 = 'A.1', 'A',

     if(Dimension2 = 'B.1', 'B',

     if(Dimension2 = 'A2' OR Dimension2 = 'B.2', 'C'))) AS NewDimension

[nsetty]

Using

a) Pick, WildMatch functions

b) Mapping table

Map_Table:
mapping Load * Inline [
Dimension2, NewDimension1
A.1, A
A.2, C
B.1, B
B.2, C
];




DataWithNewDimension:
LOAD
    Dimension1,
    Dimension2,
    Pick(WildMatch(Dimension2, 'A.1', 'B.1','A.2', 'B.2'), 'A', 'B', 'C', 'C') AS NewDimension,
    ApplyMap('Map_Table', Dimension2, 'Other') AS MapDimension

FROM [lib://mylink]
(html, utf8, embedded labels, table is @1);


Drop table Map_Table;

[danielrogra]

Thanks for your time mate. I made a mistake so I didn't explain well myself. Please see the original post

Regards

[danielrogra]

Thanks for your time mate. I made a mistake so I didn't explain well myself. Please see the original post

Regards

[danielrogra]

Anyonce who can help me?

Thanks

Regards

[joseph_eftamand]

What is your original question/post?

[danielrogra]

I want to turn two columns in just one, like follows:

Dimension1	Dimension2
A	A.1
A	A.2
B	B.1
B	B.2

into

New dimension	Total
A	sum where dimensions2 in (A.1,A.2)
B	sum where dimensions2 in (B.1,B.2)
C	sum where dimensions2 in (A.2,B.2)

[joseph_eftamand]

In the script editor you can create that logic in the load statement of the table you are loading:

if(Dimension2 = 'A.1' OR Dimension2 = 'A.2', 'A',

     if(Dimension2 = 'B.1' OR Dimension2 = 'B.2', 'B',

     if(Dimension2 = 'A.2' OR Dimension2 = 'B.2', 'C'))) AS NewDimension

You can then use that dimension in the front end and sum(quanity) for that NewDimension

[danielrogra]

Hello Joseph!

But in that case, wouldn't A.2 be only A instead of A and C?

Regards