Skip to Content
avatar image
Former Member

Count of duplicate values in a column - Web Intelligence

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 16, 2016 at 07:36 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Nov 15, 2016 at 09:47 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Nov 16, 2016 at 04:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 04:53 PM

    try this.

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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 16, 2016 at 08:27 PM

    Hi,

    Formula provided by Sushil Pandya.

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

    Attached are the sreenshots

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

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

      Appreciate it!!

      Thanks

      GD

  • Nov 28, 2016 at 05:52 PM

    Hi Gaurav,

    Glad it worked!!.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded