Best Of
Re: Is it possible to build a folder and move multiple dashboards into that folder?
@charles Yes, you can create a hierarchy of pages by creating "Subpages". You can read more about it here: https://domo-support.domo.com/s/article/360043428253?language=en_US
Re: Fixed Functions for Identifying the First and Last Connections for an Entity
@Domomon Fixed functions are only compatible in Beast Mode. If you are using MagicETL, but the Rank & Window tile provides similar functionality. Based on your use case, I would suggest using a Row Number function, sorted by Connection Date and partitioned by Entity ID in the Rank & Tile Window. Then you can use a formula tile to compare the rank to the total number of connections per Entity.
Re: Beast Mode Case Excluding Certain Strings
@Domomon the syntax is NOT LIKE when you want to be sure it's not anywhere in the string.
So:
CASE
WHEN `campaign` LIKE '%online%' THEN 'Online'
WHEN `campaign` LIKE '%residential%' AND `ga_campaign` NOT LIKE '%semi%' THEN 'Residential'
ELSE 'Other'
END
mhouston
Re: Why do all Redshift Datflows (still) convert all variables to lower case?
@user084060 I've also used Redshift quite a bit for the same reasons as your team and also found it frustrating to have all column names in lowercase, especially when using interactions on dashboards.
More recently, however, I've started using MagicETL v2 for the majority of my new dataflows instead of Redshift. The updated functionality, like formula tiles, have been able to address most of the reasons why I had originally leaned on Redshift over MagicETL. I've also found that MagicETL v2 runs much more efficiently and has more consistent processing times than Redshift. At this point, I really only use Redshift for very specific use cases, such as having to perform complex joins.
To bring this back to your original question, if Redshift's lowercase field names are causing that much of an issue for you then I suggest you give MagicETL v2 a try to see if that meets your needs.
Re: Dashboard Custom Page Breaks
I echo this idea, the ability to place custom page breaks would be very helpful! I was going to add this as a new idea, I am glad to see that others in the community agree.
Why do all Redshift Datflows (still) convert all variables to lower case?
Hi,
we're currently doing an audit and noticed that legacy datasets have 2 versions, with the 2nd version just capitalizing the variable names (often a magic ETL). The first version is in Redshift - building dataflows in redshift is quite popular within our team because it has fuctions that mysql dataflows doesn't have. When does Domo plan to add the functionality that allows to capitalize columns in redshift dataflows? It would help us to get rid of one of the 2 versions and free up row capacity within Domo.
Re: Is there a way to run ad-hoc SQL queries against arbitrary PostgreSQL tables?
@maodag Have you tried the PostgreSQL connector (non-federated)? It looks like you can write a query directly in this version of the connector: https://domo-support.domo.com/s/article/360043436273?language=en_US
Re: Getting the last field of a character-separated string
Hi @froghunter ,
Here are a couple ways to do it. If the last value is all the same length, you can use the RIGHT feature in text. If not, then you need to do the split to columns/ delimiting trick you were doing.
Here's a video that I hope is helpful: https://www.loom.com/share/3f7c949e382a442a903a32e9005f168e
Good luck,
John Le of Dashboard Dudes
You're only one dashboard away. More video solutions at: https://www.dashboarddudes.com/pantry
Re: Are Rank functions case-insensitive?
@AndreiA I would try using the str_to_hex function on your id field in the formula tile and then build the rank off of that. This will allow the rank to see those two values as different values rather than the same.
Re: Edit String in Beastmode or ETL
Hi colinr,
There are a couple of ways to do this. This can't be done in Beast Mode because it doesn't support the instr function looking from right to left.
- MySQL Flow (this is the easiest option)
trim(right(`Text`, instr(reverse(`Text`), ' | ')))
- The "text" field would be your text field. I added the trim in there because I couldn't remember if this would leave space padding on the left or not.
- ETL Flow
- Here is the whole flow. I assume you have a limit on how many texts are delimited in your field. In this example I put 6 as the max, but you can easily just add more.
- Input Data Set
- Split Column on Delimiter = " | "
- Collapse Columns to put the split columns in a row
- String Operation to trim spaces from the text field
- Filter rows to text field is not null
- Set column type. Set the rank to whole number
- Group by to get the max rank
- Inner Join back on itself to only pull through max ranks
- Select statement to rename fields and drop off some
- Here is the json code from my example flow. If you copy it, then click into some blank space on the etl, it should paste right in there.
{"contentType":"domo/dataflow-actions","data":[{"name":"test","id":"7ad5dc70-db63-4bba-8ca4-62f08d7e8073","type":"LoadFromVault","gui":{"x":24,"y":48},"dependsOn":[],"removeByDefault":false,"dataSourceId":"48368a1c-3227-436b-8e21-90088fd39c14","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"recentVersionCutoffMs":0},{"name":"Split Column","id":"f90acc6d-de26-4777-a826-943a1576fadc","type":"SplitColumnAction","gui":{"x":144,"y":48},"dependsOn":["7ad5dc70-db63-4bba-8ca4-62f08d7e8073"],"removeByDefault":false,"sourceColumn":"Text","delimiterType":"CUSTOM","delimiter":" | ","useRegex":false,"additions":[{"name":"1","dataType":"STRING"},{"name":"2","dataType":"STRING"},{"name":"3","dataType":"STRING"},{"name":"4","dataType":"STRING"},{"name":"5","dataType":"STRING"},{"name":"6","dataType":"STRING"}]},{"name":"Collapse Columns","id":"54ec4815-c991-4b0d-b456-44349e7c3965","type":"Normalizer","gui":{"x":276,"y":48},"dependsOn":["f90acc6d-de26-4777-a826-943a1576fadc"],"removeByDefault":false,"typefield":"Rank","fields":[{"sourceField":"1","typefieldValue":"1","destField":"New Text"},{"sourceField":"2","typefieldValue":"2","destField":"New Text"},{"sourceField":"3","typefieldValue":"3","destField":"New Text"},{"sourceField":"4","typefieldValue":"4","destField":"New Text"},{"sourceField":"5","typefieldValue":"5","destField":"New Text"},{"sourceField":"6","typefieldValue":"6","destField":"New Text"}]},{"name":"String Operations","id":"3d67df50-8d34-49c3-b0d9-9fec7b018d6e","type":"StringCalculator","gui":{"x":408,"y":48},"dependsOn":["54ec4815-c991-4b0d-b456-44349e7c3965"],"removeByDefault":false,"calculations":[{"calcType":"TRIM","constantB":{"value":"BOTH","type":"STRING"},"fieldA":"New Text","fieldName":"Trimmed Text"}]},{"name":"Filter Rows","id":"276293e4-d8d5-4b92-870d-c1764501ba91","type":"Filter","gui":{"x":528,"y":48},"dependsOn":["3d67df50-8d34-49c3-b0d9-9fec7b018d6e"],"removeByDefault":false,"filterList":[{"leftField":"Trimmed Text","operator":"NN","andFilterList":[]}]},{"name":"Set Column Type","id":"ad71912a-9e28-4475-b461-0b9c251bb183","type":"Metadata","gui":{"x":60,"y":192},"dependsOn":["276293e4-d8d5-4b92-870d-c1764501ba91"],"removeByDefault":false,"fields":[{"name":"Rank","type":"LONG"}]},{"name":"Group By","id":"9128dfbe-ab36-424a-90c3-3ef1a1e4c258","type":"GroupBy","gui":{"x":192,"y":312},"dependsOn":["ad71912a-9e28-4475-b461-0b9c251bb183"],"removeByDefault":false,"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"ID"}],"fields":[{"name":"Rank","source":"Rank","type":"MAX"}]},{"type":"MergeJoin","name":"Join Data","id":"c32d5a7d-ccfa-40f2-91d7-60afaa50f825","gui":{"x":288,"y":192},"dependsOn":["ad71912a-9e28-4475-b461-0b9c251bb183","9128dfbe-ab36-424a-90c3-3ef1a1e4c258"],"joinType":"INNER","step1":"ad71912a-9e28-4475-b461-0b9c251bb183","step2":"9128dfbe-ab36-424a-90c3-3ef1a1e4c258","keys1":["ID","Rank"],"keys2":["ID","Rank"]},{"name":"Select Columns","id":"904e7e19-e979-47ac-8968-2e0b25c5360a","type":"SelectValues","gui":{"x":396,"y":192},"dependsOn":["c32d5a7d-ccfa-40f2-91d7-60afaa50f825"],"removeByDefault":false,"fields":[{"name":"ID"},{"name":"Trimmed Text","rename":"Text"},{"name":"Rank"}]},{"name":"test output","id":"b3cd72e8-8090-40f7-b22d-718e19bd6a9d","type":"PublishToVault","gui":{"x":528,"y":192},"dependsOn":["904e7e19-e979-47ac-8968-2e0b25c5360a"],"removeByDefault":false,"dataSource":{"name":"test output"}}]}
I couldn't figure out how to insert photos so I've attached them in order.
I hope this helps!
Figauro