Skip to Content
0

Count of duplicate values in a column - Web Intelligence

Nov 15, 2016 at 08:50 PM

2.1k

avatar image

Hello All,

I have a Webi report with multiple columns, I'm trying to get a count of all the unique rows in column "Mesh Group" (as displayed in attachment 2). Basically, the column "Number of mesh group" which is right next to "Mesh Group" needs to display the count of all unique/duplicate values. For ex: "KM2" appears 2 times within the report, hence the "Number of mesh group" value must show 2 next to "KM2".

I have tried the count, count all & distinct functions so far, with no success. Can someone please help create a function that will achieve this for me ?

Thanks

GD

current.jpg (51.4 kB)
desired.jpg (64.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Sushil Padhye Nov 16, 2016 at 07:36 AM
0

Hello Gaurav,

Please try with ForAll Context Operator.

Formula:

=Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area])

If you dont want to consider any of the Column then remove that from ForAll Parameter.

Regards,

Sushil Padhye

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Sushil! What I was missing in your formula, is to add other dimensions that I did not specify in my question (since it's a very large table I'm working with). I later added all the dimensions under ForAll function and it worked like a charm.

Just wondering, if instead of adding each and every dimension used in the table, is there another function that will siolve the purpose, currently, if a new dimension is added to the table, the user has to be cognizant to add it to the variable formula as well.

Thanks again

GD

0
Roland Jungnickel Nov 15, 2016 at 09:47 PM
0

If the data resides on the database, you need a GROUP BY select. Just an example, plain SQL (needs to be transfered into ABAP or CDS):

Data in column "data" of table "table": A1, A1, A2, A3, A3, A4

select count(*) as counter, data
from table
group by data

result: 2, A1; 1, A2, 2; A3, 1 A4

so A1 is there 2 times, A2 one time and so on.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

thanks for the response Ronald.

I'm looking to create a variable at the report level, leveraging the available Webi functions in order to achieve this. Can you suggest a function formula that will help achieve this ?

Thanks

GD

0
Gaurav Bhatt Nov 16, 2016 at 04:15 PM
0

thanks for your response Sushil.

I tried the formula you suggested, it only bring 1s under "number of mesh group" . It does not seem to pick the duplicate values sum.

Will Distinct function help ?

Thanks

GD

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Nov 16, 2016 at 04:53 PM
0

try this.

=Sum(Count([Mesh Group];All) In ([Mesh Group]))

Show 2 Share
10 |10000 characters needed characters left characters exceeded

No luck with this formula Amit. it gibes me a constant number of 89 top to bottom.

0

Can you share the screenshot.

Or try to add other dimensions in the IN context and see

0
Jyothirmayee A Nov 16, 2016 at 08:27 PM
0

Hi,

Formula provided by Sushil Pandya.

=Sum(Count([Mesh Group])) ForAll([Network];[Mesh];[Area])

Attached are the sreenshots

Thanks,

Jothi


capture.png (18.1 kB)
formula.png (41.2 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Jyoti ! For emphasizing on Sushil's solution. It made me try hard to find the defect in my formula.

Appreciate it!!

Thanks

GD

0
Jyothirmayee A Nov 28, 2016 at 05:52 PM
0

Hi Gaurav,

Glad it worked!!.

Thanks,

Jothi

Share
10 |10000 characters needed characters left characters exceeded