Question

Remove Duplicates from Reports

  • 14 April 2021
  • 6 replies
  • 49 views

Userlevel 2

Hello Team!

I did not see this posted anywhere, we have reports where we would love to see only the unique values.

In Excel you can remove duplicate values, example, we would only want to keep the company name.

Here is my Example:

Name:     Product:                    Start Date:
TBL          C360                          1/1/2021
TBL          Success Plans         1/1/2021
TBL          Renewal Center        1/1/2021
TBL          Scorecards                1/1/2021

In this case, I would want to keep ONLY 1 of the “Company Name” I do not want to see them 4 times.If we were able to remove the Duplicate names to only keep the unique 1, that would be helpful.

And even removing the Product field it will still give me 4 values, since they are in the report 4 times.
 


6 replies

Userlevel 7
Badge

Hi @Ricardogs18 can you explain a bit more on what you are trying to report on? If you just want a list of companies, then you can add the company name to the “group by” and put the product field in the “show me” to get a count of products per company. 

Userlevel 7
Badge +1

I have an example of this that I’m dealing with right now, @dan_ahrens. I’ve helped a customer build out a data designer that has about 20 fields in the show me. This is built at the asset level and works well. Example of the data: 

 

Account Name:  Opp type:  Asset  and so on (other attributes include at-risk ARR, comments, etc.)

TBL                       MS            ABC

TBL                       MS            DEF

TBL                       SaaS         XYZ

 

The challenge is the customer wants to use the same data design for a more exec-level report where the exec doesn’t care to see everything at an asset-level but still needs to see some key fields (about 10 total fields instead of 20, so the “group by” solution doesn’t work since we’re limited to 5 fields) that are created in that design. So for the above example, they only want to see: 

 

Name:  Opp type and so on 

TBL      MS

TBL.     SaaS

 

So basically I’m no longer including “Asset” as a field in the second report and therefore only need to include data at the Opp type level. So it’d be nice to be able to drop the duplicates in this case rather than presumably have to build another data design.

Userlevel 7
Badge

I have an example of this that I’m dealing with right now, @dan_ahrens. I’ve helped a customer build out a data designer that has about 20 fields in the show me. This is built at the asset level and works well. Example of the data: 

 

Account Name:  Opp type:  Asset  and so on (other attributes include at-risk ARR, comments, etc.)

TBL                       MS            ABC

TBL                       MS            DEF

TBL                       SaaS         XYZ

 

The challenge is the customer wants to use the same data design for a more exec-level report where the exec doesn’t care to see everything at an asset-level but still needs to see some key fields (about 10 total fields instead of 20, so the “group by” solution doesn’t work since we’re limited to 5 fields) that are created in that design. So for the above example, they only want to see: 

 

Name:  Opp type and so on 

TBL      MS

TBL.     SaaS

 

So basically I’m no longer including “Asset” as a field in the second report and therefore only need to include data at the Opp type level. So it’d be nice to be able to drop the duplicates in this case rather than presumably have to build another data design.

Hi @spencer_engel can you solve this by using the “Count” and “Count Distinct” types of aggregations (this performs a type of aggregation without requiring the field to be added to the “by” area? I also might not be fully understanding your use case.

 

It might be easier to connect with you to work on this live with a screen share. 

Userlevel 7
Badge

Hi @Ricardogs18 did you figure out your reporting challenge?

Userlevel 2

@dan_ahrens The Group me is a great option, there are some cases where we need to see the various data to see where the changes maybe. A great example is when Opportunity Data is being cleansed or vetted. I can group by the Company Name, but then that just gives me a number of the stages.

Example: 

Name:     Product:                    Opportunity Stage:
TBL          C360                          Closed Won
TBL          Success Plans         Closed Won
TBL          Renewal Center        Renewal
TBL          Scorecards                Upsell

If I grouped the above, I would only get numeric values grouped by the Name I would have to ungroup it and we would still be back at Square one. Exporting and cleansing in Excel.

Data Designer has been helpful to an extent to be able to achieve getting rid of dupes.  However, DD is a little cumbersome in creating and then you have to wait for it to update.  We spend almost 4 hours on a DD that could have very easily been created in less then 30 min if we had a duplicate removal option. 

Userlevel 7
Badge

@Ricardogs18 can you share a visual example of what you would like the final output to look like? This can help me figure out the best way to get that for you. 

 

If you wanted to see a structure like the one you shared, you could do this by putting NameProduct, and Opportunity Stage all into the “Group by” and then adding some other field in the “show me” to be able to aggregate it. 

Name:     Product:                    Opportunity Stage:
TBL          C360                          Closed Won
TBL          Success Plans         Closed Won
TBL          Renewal Center        Renewal
TBL          Scorecards                Upsell

 

 

Reply