11 Replies Latest reply: Sep 25, 2018 9:01 AM by Juraj Misina

# Create dimension from two dimensions

Hi All

I have two dimensions as follows:

Dimension1Dimension2
AA.1
AA.2
BB.1
BB.2

and I want to create the following dimensions

New dimensionsTotal
Asum where dimensions2 in (A.1,A.2)
Bsum where dimensions2 in (B.1,B.2)
Csum where dimensions2 in (A.2,B.2)

How can I create that?

Regards

• ###### Re: Create dimension from two dimensions

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

• ###### Re: Create dimension from two dimensions

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

Regards

• ###### Re: Create dimension from two dimensions

Using

a) Pick, WildMatch functions

b) Mapping table

```

Map_Table:
Dimension2, NewDimension1
A.1, A
A.2, C
B.1, B
B.2, C
];

DataWithNewDimension:
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

(html, utf8, embedded labels, table is @1);

Drop table Map_Table;
```
• ###### Re: Create dimension from two dimensions

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

Regards

• ###### Re: Create dimension from two dimensions

Variation of Nagesh's suggestion:

```
DataWithNewDimension:
Dimension1,
Dimension2
(html, utf8, embedded labels, table is @1);

Left Join
Dimension2, NewDimension1
A.1, A
A.2, A
A.2, C
B.1, B
B.2, B
B.2, C
];

```
• ###### Re: Create dimension from two dimensions

Anyonce who can help me?

Thanks

Regards

• ###### Re: Create dimension from two dimensions

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

Dimension1Dimension2
AA.1
AA.2
BB.1
BB.2

into

New dimensionTotal
Asum where dimensions2 in (A.1,A.2)
Bsum where dimensions2 in (B.1,B.2)
Csum where dimensions2 in (A.2,B.2)
• ###### Re: Create dimension from two dimensions

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

• ###### Re: Create dimension from two dimensions

Hello Joseph!

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

Regards

• ###### Re: Create dimension from two dimensions

Hi Daniel,

Sorry, I'm not sure I understand, can you explain your question again? You're just creating a new dimension and assigning the text strings 'A','B','C' to different combinations of dimension2.

Perhaps I have misunderstood your requirement.

Joseph