Duplicates : counting them, not removing them?

nicolac
nicolac Member
in Magic ETL

Whats the best way to count duplicates in a table using ETL and or Beast mode on a card? Lets say I have a table of data that shows me how many times each class uses a specific tool so I end up with a table with a list of classes who have used the tool. And, each class can have multiple entries showing each time they included that tool.


I want to add a column to the table that counts how many times each unique class uses the tool so in the card to visually show the data, I want to show a list of unique classes that used the tool and then the extra column showing the number count of how many times that class used the tool.

Thanks!

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @nicolac

    Can you just do a Count(`field`) in a beast mode or do a count in a group by tile (grouping by the class) in an ETL to get the number of times each class used the tool?

    Alternatively you could just select Count from the aggregation option on your field in analyzer.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ST_-Superman-_
    ST_-Superman-_ Coach

    @nicolac

    You should be able to get the results that you are looking for with @GrantSmith 's suggestion.

    I have done something similar to measure page, or card views within Domo. I use COUNT(DISTINCT `UserID`) to show many how many different users view the page, and then COUNT(`UserID`) to measure how many page views those users have had.

    image.png


    I can also show this information over time in a graph to pick up on trends. By combining count distinct and count values, I can see not only which page had the most total views, but also which page is being used by the most users (which won't always be the same)

    image.png


    For example, I can see that I've had 179 users hitting these pages this year, but only 164 have used the main landing page. This means that at least 15 users have bookmarked our subpages and are not viewing the main page at all this year. If this number were to continue growing, it would cause me to rethink placing announcements on the main landing page as not all of our users have been using it.


    Either way, this is easily handled by a beast mode. I would actually recommend it so that your numbers will adjust as you add filters or change the timeframe that the card is looking at.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories