how to show a duplicate in a table graph

WorldWarHulk · 2021-10-21T22:24:16+00:00

There was an error rendering this rich post.

WorldWarHulk

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

Find more posts tagged with

Comments

amehdad

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

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

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

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.

WorldWarHulk

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

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.

WorldWarHulk

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:

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

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

GrantSmith

@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

Screen Shot 2021-11-08 at 12.33.19 PM.png

WorldWarHulk

I believe, I now have the correct dataset.

in the ETL, is this correct?

GrantSmith

That’s it.

WorldWarHulk

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

@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.

Screen Shot 2021-11-12 at 12.01.27 AM.png

Screen Shot 2021-11-12 at 12.02.06 AM.png

Screen Shot 2021-11-12 at 12.02.31 AM.png

Screen Shot 2021-11-12 at 12.04.33 AM.png

Hopefully this helps clear things up.

WorldWarHulk

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

That should be correct.

WorldWarHulk

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?