how to show a duplicate in a table graph

Hello Dojo!

basically, we have a dataset that shows the number of beast modes we have created along the years.

what we have is one column for the beast mode name, who made it and when.

what we do not have, and what we want is a column that identifies that this beast mode is a duplicate.

It would either show "1" or "Original" if its the oldest one

or "2" or "3" or "4" or "Duplicate" for the duplicates.

We would be using the Object_Name and Event time as the identifier if its the original or if its a duplicate.

Thanks

Comments

  • amehdad
    amehdad 🟢
    edited October 22

    Hi @WorldWarHulk, going through 'Beast Mode Manager' [https://domohelp.domo.com/hc/en-us/articles/360042925474-Beast-Mode-Manager] is your best step for cleaning up your data as you may be highlighting an original beast mode among your duplicates (using Event time) which may not be used anymore whereas your duplicates are being used in other datasets. As time elapsed since first creating a beast mode isn't always correlated to inactive beast modes, using that table graph won't highlight whether your beast is still being used in a card or not.

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @WorldWarHulk

    I'd be a bit wary of using just the beast mode name to check if it's a duplicate as you can have the same named beast mode on multiple different datasets doing the same calculations but with different columns. What I've done in the past is utilized a Magic 2.0 ETL and the Domo Dimensions - Beast Modes dataset to group based on the Dataset Name, Beast Mode name and the Beast Mode formula and done a count to verify if there are duplicated beast modes (count > 1). I've taken it a step further in my instance and filtered on Card ID being NOT NULL to see which beast modes were saved to the card and not the dataset and were duplicated across different cards for the same dataset.


    If you absolutely must use your current version and are only checking based on the name you could use a window function in a beast mode to get the row number / creation order of your different beast modes:

    SUM(SUM(1)) OVER (PARTITION BY `Object_Type`, `Object_Name` ORDER BY `Event_Time`)
    


  • WorldWarHulk
    WorldWarHulk ⚪️
    edited November 5

    Hi GrantSmith

    Where did you get the beastmode formula and cards? basically, how were you able to create your beastmode dataset?

    I am only using the DOMO activity log and then connected it to datasets and dataflow...

  • GrantSmith
    GrantSmith Indiana 🔴

    I used the Domo Governance Third Part Connector to pull the Beast Modes report and then utilized a filter, groupby and another filter in magic ETL 2.0 to determine if there were any that occurred more than once. You can click anywhere in your ETL canvas and then use CMD+V or CTRL+V to past in the code I used below to automatically populate the tiles I was using.

    {"contentType":"domo/dataflow-actions","data":[{"name":"Not Saved to Dataset","id":"d2c4d139-4bac-410f-ac73-725edb870fa5","type":"Filter","gui":{"x":366,"y":1680,"color":null,"colorSource":null},"dependsOn":["17016d2d-23ff-4d71-8593-b9e599c6f727"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Card ID","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Group By 18","id":"eb2aae43-b929-4ddf-9208-974b483da346","type":"GroupBy","gui":{"x":480,"y":1668,"color":null,"colorSource":null},"dependsOn":["d2c4d139-4bac-410f-ac73-725edb870fa5"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Dataset Name"},{"name":"Beast Mode Name"},{"name":"Beast Mode Formula"}],"partitionedAggregation":false,"fields":[{"name":"Count","source":"Beast Mode ID","type":"COUNT_DISTINCT","valuefield":null}]},{"name":"More than one occurrence","id":"95cccd1e-a9e2-4cbc-8a64-ceb62222ea72","type":"Filter","gui":{"x":576,"y":1656,"color":null,"colorSource":null},"dependsOn":["eb2aae43-b929-4ddf-9208-974b483da346"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Count","rightExpr":null,"rightValue":{"value":"1","type":"DOUBLE"},"operator":"GT","andFilterList":[]}]}]}
    

    You'll need to add your input dataset manually and connect it to the first filter.

  • I can't seem to find the Domo Governance Third Part Connector... can you point me to the right direction?

    I am able to find Domo Governance Datasets, but I doubt that that is it...

  • GrantSmith
    GrantSmith Indiana 🔴

    https://domohelp.domo.com/hc/en-us/articles/360056318074-Domo-Governance-Datasets-Connector is the KB article about it. It should be just the Domo Governance Connector. It was called the Domo Governance Thirdy Party Connector at one point.

    https://[INSTANCE].domo.com/appstore/connectors/com.thirdparty.connector.d14c2fef-49a8-4898-8ddd-f64998005600 should take you to it. just replace [INSTANCE] with your instance name in the URL.

  • thank you for all the info GrantSmith.


    This is very much appreciated.


    in the DOMO Governance dataset, these are the only columns I am getting:

    ID

    Name

    Link

    Owner_User_ID

    Created_Date

    Last_Run_Date

    Status

    Schedule

    Import_Type

    Data_Provider

    Source_Type

    Cards_Powered

    PDP Enabled

    Shared

    Account_ID


    does this mean, our DOMO admin hasnt included all columns for that dataset?

  • GrantSmith
    GrantSmith Indiana 🔴

    Can you post a screen shot of your connector and which report you have selected?

  • GrantSmith
    GrantSmith Indiana 🔴

    @WorldWarHulk

    It looks like you're using the DomoStats connector and not the Domo Governance connector. If you follow the like I posted above and change [INSTANCE] to your instance name then it should take you to the correct connector you can use to then be able to pull the beast modes


  • I believe, I now have the correct dataset.

    in the ETL, is this correct?


  • GrantSmith
    GrantSmith Indiana 🔴

    That’s it.

  • Hi Grant


    what do I need to input in number 2 in groupby?


    I can't connect the remove duplicates, it says connect to a valid source.


    also, when I try to paste your formula, here is what happens


    also, quick question, when we use remove duplicates? wont it remove the duplicates?

    remember, we need to show/identify the beastmode duplicates and show it in a card.


    Thanks for all your help

  • GrantSmith
    GrantSmith Indiana 🔴

    @WorldWarHulk

    The code I provided should have a Filter -> Group By -> Filter and it should automatically have the code pre-filled out. Do you see it elsewhere in your ETL canvas? I'm guessing because the dataflow I copied it from is quite large it might be off your screen and you'll need to scroll down. Alternatively I've attached some screen shots below outlining how I've done it.

    Hopefully this helps clear things up.

  • Hi Grant -

    Good Day!

    Yes. they were somewhere in the middle when I scrolled down.


    I was able to create a card.

    and here's what I got>>>

    with the count, I am assuming that is the number of duplicates is that correct?


    Thanks!

  • GrantSmith
    GrantSmith Indiana 🔴

    That should be correct.

  • WorldWarHulk
    WorldWarHulk ⚪️
    edited November 15

    Hi Grant

    Thank you for all your help.


    I connected the Beastmode dataset to another dataset to get the owner name.

    and I added the owner name to the groupby18.


    new question is, why am I seeing duplicates that are not grouped together? they have the same formula, dataset name, beastmode name, and beastmode formula. but is showing in a separate row?


    also, is there a way to add additional columns to show other than the ones in the group by 18?