topic Re: String Function inside Set Analysis in QlikView

String Function inside Set Analysis

Saravanan_Desingh — Sun, 29 Nov 2015 03:21:01 GMT

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?

Re: String Function inside Set Analysis

lironbaram — Sun, 29 Nov 2015 14:22:08 GMT

in this case you can use a calculated dimension

something like

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

Re: String Function inside Set Analysis

Saravanan_Desingh — Sun, 29 Nov 2015 20:20:16 GMT

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.