Best Of
Re: Remove Header Row from Table
If you only have 2 or 3 metrics to display, you can use the "Dynamic Textbox" card to display something like this:
Here are the chart properties that I used, if you had a third value to add, you could use the Penultimate Category and value macros to include those.
If you have more than 3 categories, I'm not aware of a way to remove the header.
Re: Reusable dataflows / ETLs
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 Apache-esque ETL environment).
Especially with the pending release of Magic 2.0 + Partitioning (which you're essentially describing through your incremental load process) your Dataflows are going to smoke MySQL performance.
Domo has not invested in making MySQL Dataflows faster in over 4 years and that's reflected in the version of MySQL that's the dataflows are run in (5.6 vs. 8+).
I work with 1B+ and 500M+ row datasets and have consulted 100+ clients prior to joining Sony... I know how hard a pill it is to swally Magic dataflows, there are VERY few uses cases where SQL will outperform Magic. That said Domo does have a premium feature (Adrenaline Dataflows) that allows you to write SQL directly against the database engine that Domo uses to present cards which will give you even faster performance than Magic 2.0 ... but if you're just transforming 7M I'd still point you at Magic.
What you can do is use Dataset Views (which unfortunately is still somewhat buggy) to JOIN your datasets and perform light transforms, THEN pull the DSV into Magic 2.0 for any last processing steps and materialization. If you can do your ETL phase in a DSV then you can just use Magic 2.0 for UNIONing and materializing (UNLOAD the dataset to disk in S3) the data. This would give you best of all worlds (facilitate transforming incrementally loaded data, and take advantage of the fastest ETL tools in Domo without relying too much on Magic.)
... but seriously... for 7M rows just use Magic.
Re: Reusable dataflows / ETLs
@Ben_Dunlap - I'll defer to @MarkSnodgrass and @jaeW_at_Onyx for understanding the technical workings under the hood of magic 2.0 over MySQL. It sounds to me like your hands may be tied when it comes to which type of transform you are using and the decision has already been made to use MySQL.
In that case, I would highly encourage you to filter your input datasets as much as possible and make sure that you are adding an index before any joins. You could also reach out to Domo to see if a technical consultant could review your dataflow to help you optimize it.
Out of curiosity, what are the "various reasons" that are preventing you from switching to the magic 2.0 engine? For me, we ran into some roadblocks when it came to documentation. Some of our data flow processes needed to be documented in specific ways and pasting a screenshot of the ETL or the JSON text was not sufficient.
If it is just a matter of needing to have precise control over "complicated" joins, there is probably a way to manage that with magic 2.
Re: How is remove duplicate actually work?
Ok, remove duplicates. Powerful and scary at the same time. I've killed myself over it in years past, not just in DOMO. I'm happy to share what I've learned. Hope it helps.
Here is our pretend data:
Shape Weight Color Date
Square 3 blue 2019-01-01
Square 3 blue 2019-02-01
Square 3 blue 2019-07-01
Square 23 blue 2019-05-01
Square 23 blue 2019-04-01
Square 3 red 2019-03-01
Circle 4 green 2019-01-01
Circle 4 green 2019-11-01
Circle 4 green 2019-03-01
Circle 10 orange 2019-07-01
If I use [Remove Dulicates] and key only on the `Shape` field I will only get two rows. The simple, frustrating, hard truth is that I CANNOT control which two rows. The only thing I know for sure is that both "Square" and "Circle" will be represented. Here are some thoughts on this:
Both this...
Square 3 blue 2019-01-01
Circle 4 green 2019-01-01
And this are equally possible:
Circle 10 orange 2019-07-01
Square 23 blue 2019-05-01
This is NOT possible because
- red and 23 are never on the same row
- orange and 4 are never on the same row
Square 23 red 2019-04-01
Circle 4 orange 2019-07-01
[Remove Duplicates] will not 'mix and match' values from various columns. It will pick one row and use that row.
The Real Question
I'm guessing the real question is, "How do I control which line is kept?"
This is possible. I would recommend that you use the [Rank & Window] action. If you are dealing with appended data or otherwise just want the record with the 'max date' or similar concept then also consider this article: Only using data from latest append
Rank & Window
Your first time trying to follow the setup screen for [Rank & Window] will likely be tricky, but it is not a difficult process. Honestly, filling out the boxes in Domo will be the hardest part and I think people quit there because they're not really sure it'll work anyways and when it doesn't run right they don't realize it was just a mix-match in the setup.
Before you go down this path you must ask if there is a way to identify which record you want by sorting on one or more columns. If the data is so ambiguous that you cannot create logic to single out the record you want then it won't be possible for Domo to get there either.
In our case we want the heaviest weight, and earliest date. We will use [Rank & Window] to sort the records that way and then add a `Row Number` based on that order. So 1 is the heaviest that is earliest date, and on down.
For Step 1 in the [Rank and Window Action] click "Add Function" which presents you with a new screen with it's own step 1 and 2:
- Name the field `Row Number` or something obvious like that.
- Pick 'Row Number' from the list.
- Click "Apply" in the upper right corner (you might have to scroll up). This step alone gets people lost an unable to continue.
Now, those were substeps of step 1, now we move on to the real step 2 and 3:
We will add two sets here, each with a column and sort order. The first column is `Weight` and the sort order is Descending. The second set is for column `Date` and is sorted Ascending.
Step 4 is our grouping. What field do we want to group by, in our case it is `Shape`.
Now we can preview and the data will look like this:
Shape Weight Color Date Row Number
Square 3 blue 2019-01-01 3
Square 3 blue 2019-02-01 4
Square 3 blue 2019-07-01 6
Square 23 blue 2019-05-01 2
Square 23 blue 2019-04-01 1
Square 3 red 2019-03-01 5
Circle 4 green 2019-01-01 2
Circle 4 green 2019-11-01 4
Circle 4 green 2019-03-01 3
Circle 10 orange 2019-07-01 1
By playing with various setup choices for the field and sort order in steps 2 & 3 we can change that row number order until we like what we have.
Regardless of the order, I can now [Filter Rows] and filter to `Row Number` = 1 and my final data looks like:
Shape Weight Color Date Row Number
Square 23 blue 2019-04-01 1
Circle 10 orange 2019-07-01 1
Hope this helps!!
n8isjack
Re: Is there a way to set access restriction to Administrators?
I think the other concern, even if they didn't view the specific card that you had the alert on, the admin could still just view the raw data set.
I think there is a beta feature in the works to allow "departmental admins". I don't know if the beta is actually available now or not, but you could potentially create two "departments" in your instance and keep this dataset in one department and all of your other data in a department that IT would administer.
Not really a solution (yet), but this is a problem that Domo is aware of.
Ultimately though, someone would still be the admin for your entire instance, and that admin would still have access to all data in the instance, regardless of department.
Re: Adding Space to Postal Code TEXT String in Magic ETL
@MarkSnodgrass awesome, thank you!
Re: Adding Space to Postal Code TEXT String in Magic ETL
If your postal codes are the same length and you want to add it after the 3rd character, you can do this:
CONCAT(LEFT(postalcode,3),' ',RIGHT(postalcode,3))
Community Experiences at Virtual Domopalooza
You probably heard that Domopalooza has gone 100% virtual. As we prepare to shift from a live event to a virtual event we will adjust the community experiences accordingly. We value our community members, and still want this to be a learning experience and a way to connect with other Domo users. We will provide more details about the updated format for the community events shortly. If you have questions about refunds or reservations for Domopalooza please contact your CSM. We look forward to connecting with all of in March!
Re: Replace Text Regex - IP Address Regex
You rang @MarkSnodgrass ? 😀
You're close you just need to tell the regex what groupings you wish to capture with parenthesis. Also I prefer the new formula tile as it tends to work a little bit better.
REGEXP_REPLACE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$', '$1')
For others who may come across this post as it seems you understand regex's fairly well:
^.* says to start at the beginning and match any character 0 or more times
199\.15\.
matches the IP prefix filter
([0-9]{1,3})
Matches a number (0-9) 1-3 times and stores it in a match group. the first is stored in $1 and the second is stored in $2
.*$ says to match any character 0 or more times at the end.
I'm matching the entire string ^.....$ in this case because I only want to pull out the specific value. Domo's regex doesn't have a way to specifically pull out a match so we need to replace the entire string with the value we're looking for. A caveat to this is that it will return the entire string if it doesn't match the regular expression. If you want to return an empty string instead of the full string you could do something like:
CASE WHEN REGEXP_LIKE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$') THEN
REGEXP_REPLACE(`ip_address`, '^.*199\.15\.([0-9]{1,3})\.([0-9]{1,3}).*$', '$1')
ELSE
''
END
Re: Line Graphic with YTD Data (cumulative total each month)
@bp_paulo_fernandes have you looked at @GrantSmith 's post regarding running totals in beast mode?
https://dojo.domo.com/discussion/52681/domo-ideas-conference-beast-modes-running-totals#latest
He lays this out pretty nicely.