Best Of
Re: Case Statement with multiple conditions and excluding null values
If you're only wanting this to process for Account A, I would a nested case statement like this:
(CASE WHEN TRIM(`column1`) = 'Account A' THEN (CASE WHEN TRIM(`column2`) = '' THEN `column1` ELSE `column2` END) ELSE `column1` END)
Re: Help with Beast code to do color coding on the data..
Have you tried a Heatmap table card? You can use the chart properties to select the color theme as well as which columns in the table should have the colors. You also get the option to use the same scale for all columns, or a different scale for each column.
Re: How to share specific views of a dashboard to different stakeholders
Hi @ozarkram
It sounds like you're wanting to restrict which data is visible depending on specific user criteria. This is what PDP was designed to do. You can enable PDP and define the criteria for the records users or groups are allowed to see. You can read up on PDPs here: https://domohelp.domo.com/hc/en-us/articles/360042934614-Creating-and-Deleting-PDP-Policies
Re: Replace an excel file uploaded using File Upload Legacy connector
If you go to the dataset in the data center and click on it, you can then go to the settings tab and re-upload a file.
Re: Trying to use SSMS to connect to Domo Server via Domo ODBC Driver
@Jack_Kaye to be clear you're trying to query Domo, NOT query an on prem database?
i'm sure it can be done but I'm not sure you're gonna get the desired end-user experience...
Adrenaline wasn't really designed like Snowflake or TSQL in a cloud environment where you should be able to query the database with an IDE. The ODBC driver doesn't (AFAIK) get a lot of use so... i'm not sure how developed a feature it is.
if it were me, I'd construct my query in Dataset Views in Domo and then just use literally any other (free) tool, Python + APIs or Domo CLI to export the data when i need it.
Re: Why is there a large performance hit for mixing columns from a join statement?
indexing IS very dataset specific. and you're using MySQL 5.6 so all the updates in the platform in the last ... 10? years are not in place. so yeah... a DBA is your best bet if you insist on optimizing your query performance by hand.
PS in MySQL dataflows you can use the 'explain sql' button (it's a drop down under "run SQL" ) to show the execution plan which will give you a hint as to whether your dataflow is even taking advantage of the index.
also keep in mind each transform will CREATE TABLE so you have to index your intermediate steps if you're going that route.
i have implemented dataflows in Domo for over 100 customers ... i can count on one hand the number of design patterns that absolutely cannot be done in Magic... with the advent of FORMULA tiles and integration of SQL-esque functionality in different parts of the Magic platform... most jobs can be shifted into Magic 2.0 and perform pretty quickly.
alternatively, divide the task. do the JOIN in a dataset view and the row level transforms in Magic.
Re: Forecast monthly expense
instead of adding columns, consider adding rows.
consider trying to create an 'accruing_schedule' dataset that says
accrument_date, original_receipt date, po_id, po_id_accrument no, PO_amount, accrument_amt
so if your PO_amount was 10,000 and it was to be accrued over 4 months
you'd duplicate the row 4 times, you'd increment the accrument_date for the first (or last... whatever) day of each month. 10,000/4
po_id_accrument no
PO123_0422
PO123_0522
PO123_0622 etc.
that way you could filter accrument_date in the last 6 months and say "this is how much we accrued in the last 6 months across all your POs
if you UNION transactions, make transaction date the same as the Accrument Date and you can filter on activity_date in the last 6 months and have a column for expenses and accrument.
Re: Show players name based on score in domo summary number
@MarkSnodgrass and @ST_-Superman-_ have super creative ideas... but i strongly recommend calculating this in ETL... your card won't respond to page filters
and if users aren't using filters then you're probablly creating single use datasets which isn't ... ideal.
consider building a table card with a LIMIT=3 (down in the data table
then just calculate rank() order by sum(amount) desc.
Re: Show players name based on score in domo summary number
If you want to display values in a notebook card using summary numbers, then they need to be values and not text fields. I noticed all of your players could be identified by a number so I took a stab at this one. I had to use an ETL to help with this prior to creating the card. I used magic etl to alter your table to be something like this:
You can copy and paste this into your etl to see how I did it:
{"contentType":"domo/dataflow-actions","data":[{"name":"Dojo vgupta","id":"92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"86eb1e4b-5fa8-4dfd-b5ce-1167d5288d5e","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Total Score","id":"c19f4ca6-a0e0-42d4-8adf-64ec88f08508","type":"GroupBy","gui":{"x":168,"y":48,"color":null,"colorSource":null},"dependsOn":["92c4f7a9-7617-4e4d-bc0c-01cd2d9db890"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Player"}],"partitionedAggregation":false,"fields":[{"name":"TotalScore","source":"Score","type":"SUM","valuefield":null}]},{"name":"rank","id":"2ad36901-9f4a-4b41-b8de-99ceba909b15","type":"WindowAction","gui":{"x":276,"y":48,"color":null,"colorSource":null},"dependsOn":["c19f4ca6-a0e0-42d4-8adf-64ec88f08508"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Rank","operation":{"type":"RANKING","operationType":"RANK"}}],"orderRules":[{"column":"TotalScore","caseSensitive":false,"ascending":false}],"groupRules":[]},{"name":"result","id":"5d2dea58-d0d8-4001-a86b-2f01df0308d6","type":"ExpressionEvaluator","gui":{"x":408,"y":48,"color":null,"colorSource":null},"dependsOn":["2ad36901-9f4a-4b41-b8de-99ceba909b15"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"Case when `Rank`=1 then 'winner' when `Rank`=2 then 'runner up 1' when `Rank`=3 then 'runner up 2' else 'participation trophy' end","fieldName":"Result","settings":null}]},{"name":"Join Data","id":"b4d5e0f1-0037-45d7-a1f1-2e8295a1e2ab","type":"MergeJoin","gui":{"x":528,"y":180,"color":null,"colorSource":null},"dependsOn":["92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","5d2dea58-d0d8-4001-a86b-2f01df0308d6"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"92c4f7a9-7617-4e4d-bc0c-01cd2d9db890","step2":"5d2dea58-d0d8-4001-a86b-2f01df0308d6","keys1":["Player"],"keys2":["Player"],"schemaModification1":[],"schemaModification2":[{"name":"Player","rename":"","remove":true}],"partitioningInputId":""},{"name":"Add Formula","id":"ff7d2290-33a3-4108-baf5-ad8d8a685566","type":"ExpressionEvaluator","gui":{"x":636,"y":180,"color":null,"colorSource":null},"dependsOn":["b4d5e0f1-0037-45d7-a1f1-2e8295a1e2ab"],"removeByDefault":false,"notes":[],"expressions":[{"expression":"trim(leading 'player' from `Player`)","fieldName":"Player number","settings":null}]},{"name":"Alter Columns","id":"bf2e1f9f-a71b-4527-9e1d-442033855ed7","type":"Metadata","gui":{"x":744,"y":180,"color":null,"colorSource":null},"dependsOn":["ff7d2290-33a3-4108-baf5-ad8d8a685566"],"removeByDefault":false,"notes":[],"fields":[{"name":"Player number","rename":null,"type":"LONG","dateFormat":null,"settings":null,"remove":false}]},{"name":"ETL vgupta","id":"4a34775f-41a2-488a-b879-acefadf6f14d","type":"PublishToVault","gui":{"x":924,"y":180,"color":null,"colorSource":null},"dependsOn":["bf2e1f9f-a71b-4527-9e1d-442033855ed7"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"5e12f8a1-f879-44bd-83f5-f708ed9d7671","type":"DataFlow","name":"ETL vgupta","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[]}]}
You can now filter your summary number based on the result value, like this:
Same for the Total Score
This lets you end up with something like this:
You can also use a calculated field to create a long summary number on a visualization card:
CONCAT( 'Winner: ',max(case when `Result`='winner' then `Player`end), ' | ',MAX(case when `Result`='winner' then `TotalScore` end) ,' - Runner Up 1: ',max(case when `Result`='runner up 1' then `Player`end), ' | ',MAX(case when `Result`='runner up 1' then `TotalScore` end) ,' - Runner Up 2: ',max(case when `Result`='runner up 2' then `Player`end), ' | ',MAX(case when `Result`='runner up 2' then `TotalScore` end) )
Re: Admin Question - Unable to move page?
When you select "Top level page" the search bar next to "Make subpage" will be grayed out. You then just have to select the permissions for the page and you will be able to select "Move Pages".
