Best Of
Re: Is the SQL Tile in Magic Broken?
Thanks for surfacing this. Are you all using window functions in your SQL tiles where you are seeing this issue? Also, can you message me the ticket numbers you filed? I'll ping my engineering squad on this.
Re: Is the SQL Tile in Magic Broken?
Thanks Andrea, looks like the ETL's are running sucessfully now.
Re: Is the SQL Tile in Magic Broken?
Yes, this is happening in SQL tiles with window functions for me.
Re: April Community Forum Recap
Unfortunately, that ignores @ggenovese achievement. Apologies, and belated congratulations to Giacome.
Re: Dynamic Variables: Dropdown Selector Compatibility and Multi-Select Support
It doesn't matter what you populate the variable with when you originally create the variable, because that will be overwritten with the dynamic values later. You populate the dynamic values by creating a slicer card that has those values, then on the App, you select the "action" for that slicer card, and instead of filtering, you select the action for populating the variable.
- Create variable; values don't matter.
- Create BeastModes using variable on at least one card.
- Create slicer card using column of dataset that has values you want to be able to use in variable.
- Go to edit the app > select the slicer card > Actions > Set variable
Re: Where is the best starting point to learn about Workflows?
Tom, welcome to Workflows! They are so powerful, but do have a little steeper learning curve.
Have you taken the "Getting Started with Workflows" training at learndomo.domo.com?
If I were building this, here's what I would do:
- Start with the loop over dataset template
- Query for a distinct list of dataflow owners
- Loop through that list of dataflow owners, and for each
- Run a query that will return all the dataflows that meet your criteria (a where clause for the owner, the desciption, the last run date > X, etc…
- Once you have that list back, I'd create a custom function (AI can help with this - ChatGPT can do it if you're unfamiliar with JS or Python) that takes my list of objects (an object being a row of data from your query) and turns it into a string which is an HTML table.
- Insert that HTML table along with some other text in the sendEmail function
Beastmode listagg/group concat
Hello,
I usually refer to the list of magic etl functions documentation for functions that I can potentially use in beastmode since some working functions do not exist in the list within the beast mode editor.
However, a function that can combine strings from multiple rows into a single string like GROUP_CONCAT and LISTAGG does not work in beast mode. Is there any alternative to this?
By the way, the reason I don't do it in the ETL or SQL is because I want the output to be responsive to card filters.
Here's a particular use case, if I have a dataset with the fields: accounts, end users, date, and products, and I want to see all the products the accounts have sold to their unique end users in comma-separated values, and I want it to be responsive to filters. Say, if I filter only for the last 12 months, and the concatenated products should only show all the products sold in the last 12 months without duplicating the accounts by end user rows.
Thanks in advance
rmbourne
Re: SQL Script
Magic ETL is an in-house stream-processing engine with SQL support added later. There is no underlying SQL engine; SQL gets converted to an optimized graph of actions like the ones you can configure on the canvas.
rco
Re: SQL Script
Aggregates without a group by are universal and standard. The presence of aggregate functions treats the entire table as a single group if there is no group by.
Non-aggregated columns in an aggregated query are less common, but MySQL supports it with ONLY_FULL_GROUP_BY=false (which was the default before 5.7) and SQLite still supports it. In both of those systems, the row that is used to compute the values for the unaggregated columns is undefined.
Magic ETL follows that less common tradition. Non-aggregated columns are allowed in aggregate queries and they are computed from an arbitrary row in the group. If your SQL is simple (e.g. SELECT *, SUM(a) as sum_a) then you can count on it using the first row for each group.
rco