0

Identifying Duplicated Elements

Hi all, 

I have a discover object containing products and their status. 

I want to identify products where the attribute status is duplicated. i.e. Products where the status has been assigned more than once as '1 and 2', not just '1 or 2'. 

I have tried this several ways, but cannot get it working:

- Using Members Search to select '1 and 2'

- Creating a custom member for products which meets both conditions 

Any help would be greatly appreciated!

Thank you,

Jenny Euesden

6 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Jenny,

    This problem falls into the category that requires analysis / calculation from the underlying table of data that makes up your dimension / attributes. You would not be able to ascertain your selection criteria from the Hierarchy / Attribute as these items consist of a list of unique values from the table, rather than all the values, hence your lack of success!

    Can you confirm what underlying database technology you are using? Is it SQL based (our IMDB or other SQL engine like SQLServer or Oracle) or are you using Analysis Services or SAP BW cubes ? If it is the latter, then there is no way for Pyramid to calculate this as we do not have access to the underling data that was used to build the cubes.

    Thanks,

    Ian

    • Jenny_Euesden
    • 1 yr ago
    • Reported - view

    Hi Ian,

    Thank you for your reply.

    It is direct query data source which connects to Redshift Prod - is it possible for Pyramid to calculate it with this as the underlying database technology?

    Kind regards,

    Jenny Euesden

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Thanks Jenny,

    Yes, it should be. One more question, is your Status field in the same table as the Product Field? If not, how are the two related?

    Ian

    • Jenny_Euesden
    • 1 yr ago
    • Reported - view

    Hi Ian, 

    Yes they are in the same table. Please can you let me know the steps to do this calculation?

    Thank you,

    Jenny

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Jenny,

    In your hierarchies panel, right click on Product and choose "Aggregate / Count". Your menu may look different to mine as you will be on an earlier release, but you should have the "Count" function available somewhere.

    Make the Measure shareable so that we can use it in Formulate:

    Create a Discover view with Product and the new Count Measure. This will tell you how many times each product appears in the table.

    I've also put a total on the top which shows how many records there are in this table.

    Now we need to examine the number of times a product has Status A and how many times a product has Status B. In fact we want to know which Products have both Status A and Status B more than once each. In my example I wil use "Socialdiscount" which has values of Yes, No and NULL. I'll use Yes and No.

    Create another new Measure using Formulate like this:

    This is saying, If the count of Product with Status A (SocialDiscount, No) is greater than 1...

    ...AND the count of Product with Status B (SocialDiscount, Yes) is greater than 1 ,then set the Measure to 1 otherwise 0. When we place this Measure, Flag, on our grid above, those Products that have Status of A (SocialDiscount, No) and B (SocialDiscount, Yes) more than once each will show as 1. We can then filter on Flag to only show those products where my test is true:

    So in my data, 157 products, listed, have a Status of A and B more than once each.

    Hope that helps.

    Ian

    • Jenny_Euesden
    • 1 yr ago
    • Reported - view

    Thank you Ian! This was very helpful.

    Regards,

    Jenny Euesden

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 6Replies
  • 63Views
  • 2 Following