jaeW_at_Onyx Coach

@Matt_Umolac93 typically for trouble shooting window functions you should separate components into separate columns. i.e. put the numerator in one column and the denominator in another. this way you can isolate the culprit. are you confident your CASE statement is working as expected? if you write sum(sum(amount)) over ()…

there is an API for sharing. if you share a page with a user all the cards are shared automatically, but i believe domo changed functionality a few years ago so all the datasets aren't shared automatically. i may be mistaken (@GrantSmith ?) but if you share datasets with a group, all the users would receive access at once.…

@mberkeley the SQL driver is built by Netsuite. Netsuite would have to build that functionality into the driver before Domo's connector would support it. Also purely from a best practices point of view it might make sense to import the data as is. yes it does lead to higher data volumes, but in Domo for filtering purposes…

looks like you could split your data using a comma delimiter. have a look at the split_part() function in MagicETL > Formula tile from there you can use the same techniques laid out in this video, https://www.youtube.com/watch?v=oYcpYE7DiV4. if you need to start on just the content between the square brackets [] then start…

@jrtomici what is a "header row". if it's a column that labels or identifies a row, in traditional database nomenclature we'd just call that the row_id or primary_key. if that's the case, then there's no reason to import as a crosstab. just import it as a normal dataset. then you can use filters to choose which rows to…

@pstrauss splitting multiple multi-value columns into rows would cause massive row explosion. imagine one row with 3 values in column a and 3 values in column b. you end up with 9 rows. if you do that across multiple columns, huge row growth. instead, consider a dimensional approach. take each of your multivalue columns in…

uh... would it be fair to say you want to rank orders by date ascending, and just want to know the date_diff of the first order and today? do your rank() function in ETL and then do your datediff in Beast Mode.

remember zero and null are not the same. assuming your beast mode is sum(values) / count(values) if you had 5 rows 0,0, null, 2 and 6, the average is 8/4 = 2 if you had 5 rows null, null, null, 2 and 6 the average is 8 / 2 = 4. if you are doing division within the row avg( colA / colB ) as opposed to sum(colA) / sum(colB)…

what's your beast mode?

https://www.youtube.com/watch?v=cnc6gMKZ9R8

@yli_2596 yes. https://domohelp.domo.com/hc/en-us/articles/360042926274-Adding-a-DataSet-Using-a-Connector#3.3.1.

Thinking it through another set based way of approaching this without variables would be to do what SQL has to do under the covers (generate windows) for each row in your dataset we'll generate the 4 rows, then we'll assign the value of the transaction for those lagged rows if they exist, then we'll aggregate. this assumes…

+1 on Grant's solution using Lag(1) .. Lag(2), Lag(3) IF YOU DON"T HAVE GAPS IN YOUR DATA. Only thing I strongly dislike about this approach is that you have to create 3 functions (not efficient) AND it must be true that there are no gaps in data. Computationally, @GrantSmith i would say this is probably the only good use…

you can't create graphs based on bucketed activity that's been aggregated in analyzer. you'd have to materialize the buckets in ETL or a dataset view and then create a graph on that column. Do an ETL where you COUNT and GROUP BY your Axis then JOIN that dataset back to the original transactions unfortunately that means…

@BeastMode since you're asking a mysql question, stack overflow would probably be your best bet. but... let's close the loop here. why you trying to dynamically add 'missing columns'? how are you defining what's 'missing'? buuuut before we even address that question. if i had to quess you're trying to union multiple…

If this is going to be a recurring activity create a Dataset View https://domohelp.domo.com/hc/en-us/articles/360046074774-DataSet-Views

+1 with @MarkSnodgrass there is no reason to calculate your metrics in beast modes, it would just be a slow card with difficult rules. You'll want to use a LAG or LEAD function which is explained here in Beast Modes, but the concept is consistent across Magic and SQL: https://www.youtube.com/watch?v=cnc6gMKZ9R8 Given the…

Dali did you want COUNT(DISTINCT) instead of COUNT? If so, you cannot COUNT DISTINCT across rows in a table card or graph. You might UNION a dataset to your dataset of just distinct customers.

oh goodness... defo use @MarkSnodgrass method. it's easier to read and stores the data as type DATE which will have more flexibility in cards than type TEXT

@MarkSnodgrass + @pstrauss before you pivot, make sure to assign a value to how to prioritize the match. i.e. matchValue =1 if JOIN on Name matchValue = 2 if JOIN on Description matchValue =3 if JOIN on then Pivot then JOIN then RANK on MatchValue desc then filter where Rank = 1 to only keep one version of the row. Good…

@DataMaven i believe Domo are using Zendesk...

@imelendez Stacker was built back in Magic 1.0 era. Magic 2.0 should outperform virtually every ETL engine EXCEPT Adrenaline Dataflows and in some cases Data Set Views. UNLESS you are doing something that a database is MUCH better at (GROUP BYs, Window Functions and JOINs). But even then, to opt for a SQL dataflow option,…

@vikrao what's the business question you're trying to answer in plain english? i suspect it's something like "which users havent paid an invoice in the last x months. if so rework your filter as a formula based off CURRENT_DATE

@Nathan + 1 to @ST_-Superman-_ and @Grant their advice is sound. I know it's not the answer you want to hear, but next step is definitely to not have 150 cards on a page. you'll get the biggest gains here. while domo can render multiple cards simultaneously, you ARE issuing queries to a database, so subsetting your page…

RANK & WINDOW + Filter will initially output the same number of rows that came in. If Window Functions are not necessary, and you just need a SELECT DISTINCT equivalent, REMOVE DUPLICATES is OK except it keeps all your columns, so a good alternative (but equally slow to process) would be a GROUP BY with a Min / Max or a…

@Ben_Dunlap Ooof... migrated away from Magic. ooook. are you familiar with recursive dataflows? I cannot ... de-recommend them strongly enough but it is an anti-pattern that works. You can add indexes to your MySQL dataflow which may boost performance during the JOIN action…

@Ben_Dunlap Flat out, with only 7M rows of data you (99% guaranteed) will have the best and fastest performance with Magic 2.0. The underlying transformation engine is more performant than the way data is piped in a MySQL dataflow (out of Domo Vault / Amazon S3 and into a MySQL VM and then back the S3 vs. streaming in an…

use splitpart to split the text strong on the comma delimiter split_part(`addressfield`, ',', 2) wrap those results in a trim to remove any leading whitespaces. then split_part on the space and keep the first result in the array

one of the nice things about custom apps is that when you sync appdb document collections to the datacenter, they sync with UPSERT dataset enabled. to understand how to delete rows from a dataset with an UPSERT key, use that java cli and log-api to monitor which api is hit as you update datasets via cli

you can't do a count(distinct) across rows in a table card. if you take the sum of count(distinct) it assumes that an account does not exist across multiple statuses. unfortunately, you cannot count(count(distinct)) b/c the number of accounts would have already been aggregated. instead you could UNION a set of rows…