String Function inside Set Analysis

[Saravanan_Desingh]

I have the below Table

A1	10
B2.5	8
c3	2
B2	264
C3	890
D4	5

I am expecting the below result

B2	272
C3	892

Basically B2.5 have to be replaced with B2 and c3(lower case) has to be replaced with C3.

I tried the below Set Analysis. But not working.

Count({$<[Dim1]={'=Replace(B2.5,B2)',"=Replace(c3,C3)"}>} Dim1)

We can replace this in the LOAD statement. But I don't want to change the original data. I only want to do it thru UI Objects. Can you please suggest any solutions here?

[lironbaram · Accepted solution]

in this case you can use a calculated dimension

something like

upper(left(Dim1,2)) as your dimension

[Saravanan_Desingh]

Hi Liron. Thanks for your response. I was able to replace the values using Calculated Dimension as below.

=Replace(Replace(F1,'B2.5','B2'),'c3','C3')

But I am getting the below output

=Replace(Replace(F1,'B2.5','B2'),'c3','C3')	Sum(F2)
A1	10
B2	272
C3	892
D4	5

If I tried to filter using the below expression

Sum({$<F1={"B2",C3}>} F2)

I am getting this.

=Replace(Replace(F1,'B2.5','B2'),'c3','C3')	Sum({$<F1={"B2",C3}>} F2)
B2	264
C3	890

It looks like the calculation done at Dimension level is not getting reflected in the Expression.