Best Of
Re: Is 'Partition by' supported in DOMO SQL?
@msharma be specific about WHERE you are conducting your ETL.
It's imprecise to call anything "Domo SQL" and no one will know exactly where you are in the product.
@MarkSnodgrass is correct MySQL dataflows do not support Window functions. It's not because it's a 'Domo flavor of SQL'. MySQL dataflows are running in MySQL 5.6 databases. and 5.6 did not support Window functions.
SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`, sum(sum(case when `Transaction Type` = 1 then `Amount` else 0 end)) over (partition by `G_L Account No_`) as NewSum from g_l_entry
This is not appropriate SQL syntax. You wouldn't write a nested SUM(SUM())
SELECT `G_L Account No_`,`AcctLen`,`fund#`, `Fund No_`,`Dept#`, `Obj#`, `Global Dimension 1 Code`, `Posting Date`, `Description`, `Amount`, `Transaction Type`, `Actual_Amt`, `Budget_Amt`, sum( case when `Transaction Type` = 1 then `Amount` else 0 end) over (partition by `G_L Account No_`) as NewSum from g_l_entry
This is appropriate SQL Syntax. No, in MySQL / Redshift when writing SQL queries you are not required to include a GROUP BY clause.
Given that you wrote Sum(Sum(Amount)) I assume you're trying to build a window function in Analyzer / Beast Modes. Again, while you might be tempted to call it Domo SQL, it is more precise to specify that you're writing queries that will hit Adrenaline (because that's the name of Domo's database layer).
In that case, yes, Window functions ARE supported, but you must be applying a GROUP BY clause in the query that gets sent to Adrenaline via Analyzer. So you MUST have some sort of aggregation in place.
Also, the interface between Analyzer and Adrenaline is a bit weird so yes, you MUST have the nested aggregate function SUM(SUM()) which is not standard SQL.
Re: Is there away to pull the Ticket updates dataset in Zendesk using any othe the Domo connectors?
@kstaufferTek , what problem are you having?
This is probably a question best directed at support@domo.com
Re: Dividing in SQL
Hi @gbennett
Did you mean to say aggregating it doesn’t work? How does it currently work and how are you expecting it to work?
Re: Dividing in SQL
Given the math you've put on the screen, there is no reason to run this ETL as a SQL transform. Just keep your data unaggregated.
Be careful with
Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total Low to High'
for any row, if any column contains the value null, the entire result will be null. this is the appropriate adjustment.
Sum(ifnull(`Early to Lost`,0) +ifnull(`Early to Won`,0)+ifnull(`Early to Late`,0) ) AS 'Total Low to High'
to do the division you're describing, just calculate the ratio in a beast mode (you may have to build the beast mode in analyzer, then share it to the dataset in order to reference it in a sumo card.
Personally i recommend you don't use the Sumo card and just use the Pivot Table card in Analyzer.
Re: Calculation of a row value by the Grand total for that selection
What you're describing is a window function. If you abstract your problem and google "percent of total" SQL, you'll find loads of solutions.
You'll have to ask your CSM or support to enable "Window functions in Analyzer" but after that
sum(sum(hours_worked)) over () will give you the 56
Re: Duplicate rows on Workfront Advanced Project report set for Replace updates
The rows are greyed out because domo has archived off those executions so you can't restore from those versions.
Re: User Profile fields only manual?
+1 for the APIs. They are great for automating some tasks like this.
Personally, I've used the PyDomo package and a python script to interface with the Domo API to update the user attributes.
Re: Jupyter workspace
Hi John (@Jobur )
Jupyter workspace is all self contained within the Domo environment. You don't need to have anything installed locally.
If you want to do your analysis locally you could get python / jupyter installed on your local machine and then utilize the pydomo package to pull data from Domo as well.
Domo cards are great for getting quick looks at your data and Dataset Views (in beta, talk with your CSM to get it enabled) are good as well to get simple aggregate views of your data as well.
Re: "Failed to start data processing engine"
Hi @eric_tetik
You'd want to reach out to Domo support as it's an issue behind the scenes on their end and they'd be able to get you a better understanding of what the issue is since they have access to those logs.
Re: Tooltip text in a card
Hi @user01061
You can't dynamically set a column header in a table card. You can use a column's value as tooltip text which you can then use the tooltip macro in the hover text or data value properties but the actual column names can't be changed dynamically.