Best Of
Re: Returning a Max Result in a column
Try converting to a window function so that each row is compared to a single value instead of the aggregated value.
CASE WHEN `D's % of AOR` = MAX(`D's % of AOR`) OVER () THEN 1 ELSE 0 END
Re: How to get the first character of a string in domo beast mode analyzer
Perfect this worked thank you
Utz
Re: DomoStats dataset - DomoEverywhere subscriber instances
@akeating as a workaround, it looks like there is a product API you can hit that will return a list of your subscriber organizations:
GET https://{{instanceName}}.domo.com/api/publish/v2/controlcenter/subscribers?limit=200&offset=0
ellibot
Re: Can I run a Stored Procedure to an external MS SQL and import resulting dataset?
Hi Barb,
Yes, you can using something like the following:
exec [yourStoredProcName]
select * from [yourTableName]
This will work in workbench or the MS SQL Server connector. You may have issues depending on how long it takes for the table to be produced. If this is a lengthy process, then you might be better off scheduling the stored procedure as a SQL Server Agent Job and scheduling Domo to update after the table has been updated.
Chris
Re: Add proxy logins from domosupport to activity log domostat
@ColemenWilson it looks like the Activity Log API might provide information about proxy logins, so you could theoretically create your own DomoStats for this info:
ellibot
Re: ETL joining causes an output dataflow that is too big?
Hi @user04285 welcome to the community, and to Domo.
Magic ETL can be incredibly powerful, here are a few Magic tips that I think may be helpful in your situation:
- Start simple/small, trying just running this as a simple join between two of your datasets before bringing in the third. Take care to note the differences between Inner, Outer etc.
- Make sure there are no duplicates in your Key Column, you can do this by putting a "Remove Duplicates" function on the data thread before plugging it into the join
- Employee Name is typically a tough name to use as a Join Key because it is often free-form. I always look for a system-generated (optimal) or at the very least an identifier field that is numeric (so maybe Employee Name)
- Lastly, a bit of a nuance with a Left Outer joins. Sometimes you will get an error message that says "Greater than 10K duplicates, can't be joined". In those cases, you can change it to a Right Outer (and reverse the dataset positions) and it will work
All that being said, if you will upload a couple screen shots of your datasets and the Magic ETL itself I am happy to have a look!
Chips
Re: Filtering strings that contain letters
^ - Start of the text
$ - End of the text
Combining them together in the rexex above simple enforces that the entire string is made of numbers instead of just finding numbers anywhere in the string
Re: How do I Compare Column A vs Column B in the same Value?
One way you could do it is the beastmode below:
SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) THEN `Sessions` ELSE 0 END) - SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) - INTERVAL 1 YEAR THEN `Sessions` ELSE 0 END)
If this doesn't work let me know!
Re: Domo Office Add-In - Relative Date Filters
I've got this scheduled for the next month or two in our monthly release cadence for the addins.
(Internal Tracking: ID-411004)
I will report back once we roll this out.
Thanks everyone for your feedback.