Best Of
Re: Filter in magic ETL
I think David's reply is correct if you don't have a rank field yet. If the rank field already exists, you instead would have to group by the id to find the max(rank), and inner join that back to the data, with the join fields being id = id and rank = max(rank).
Re: Filter in magic ETL
Yes - you can use the Rank and Window tile to create a rank based on your id. Then use the filter tile to return the rank you want. If you want the min id you can just sort ascending on your rank/window function and then filter to where rank=1. If you want the max id, you can sort descending and then filter to where rank=1. In your case, if you're wanting the min rank per group. You would need to partition by ID to start the rank over again for each id.
For example. Input dataset
The above is how you could set up your rank/window tile.
Example output
Filter to where rank = 1
This output matches your example requested result.
Re: Switching a Dataset to a Workbench
Hello @ColinHaze,
You can configure a workbench job to use an existing dataset by expanding the Domo Details section. Next to the Dataset Name is a browse button. Press this button. Click on the Connector DataSets tab and locate the dataset you want the workbench job to use. Click the Select button. I would recommend making a backup of your dataset just in case something goes wrong so you do not lose all of the data unless it is something you can reupload easy enough.
Chris
NEW Domo Data Science Resources Guide (help w/ Jupyter, scripting tiles, data science tiles, AutoML)
Hi all, I'm a data scientist at Domo on the Data Science Professional Services Team. We have a NEW Domo Data Science Resources Guide.
The guide includes a list of all the help resources Domo has created related to data science products (Jupyter Workspaces, Magic ETL R/Python scripting tiles, Magic ETL data science tiles, & AutoML), as well as a list of webinars on data science topics (forecasting, data science readiness, ChatGPT, etc.). This guide will be updated over time as more help resources become available.
If you have suggestions on other help resources that would be useful to create or data science topics that should be explored in a webinar, comment below or contact the team at datascienceSME@domo.com
Re: Break out rows depending on columns having a value
@jrtomici the answer provided by @ColemenWilson is the correct one, and I kindly ask that you please select his answer as the best response. I just wanted to provide you with a quick breakdown of how to achieve it.
Example Data
You'll use the dynamic unpivot tile, selecting "Name" to not be pivoted and then assigning whatever name you want to give your column name and column value columns. In this case I choose ID and Value
Then you can filter to where Value is not null to remove rows where there wasn't a value for that ID
Lastly, you can select the columns you desire in your output. In this case, I matched the output you showed in your post.
Re: I want to show % of year...how do I do that?
@Bkinzel_21 You can create a Beast Mode tooltip using a FIXED function.
- Add the following Beast Mode to calculate the % of Year:
sum(`Result`) / sum(sum(`Result`)) FIXED (by `Year`) - Drag the new Beast Mode to the Tooltip 1 spot (if you don't see this you will need to click the Tooltips button on the top ribbon to make it visible)
- Add Tooltip 1 to the text box in Data Label Settings or Hover Text Settings under Chart Properties
More on Data Labels and Hover Text:
More on FIXED functions:
Re: Pivot/unpivot help Magic ETL
@Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).
Re: Changing data from text to integer
REGEXP_REPLACE(`field`, '^.* ([^ ]+)$', '$1') * 1
this should find the last value in your string after the last space. Multiplying it by 1 will convert it to a number.
Will the numbers be always at the end or anywhere in the string preceded by AUD?
Re: Concatenating amount and number fields WHILE also formatting them?
@AngelaO415 here you go. A few things to keep in mind. This beast mode works for whole rounded numbers, so it won't format decimal places correctly. I went ahead and configured it for up to 10million. If you want to expand it you can do so following the same pattern. If you need help expanding, let me know.
And here is the beast mode
CONCAT('$',
CASE
WHEN LENGTH(ROUND(`request_amount_recommended`)) < 4 then `request_amount_recommended`
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 4 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,2,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 5 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 6 then CONCAT(SUBSTRING(`request_amount_recommended`,1,3),',',SUBSTRING(`request_amount_recommended`,4,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 7 then CONCAT(SUBSTRING(`request_amount_recommended`,1,1),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
WHEN LENGTH(ROUND(`request_amount_recommended`)) = 8 then CONCAT(SUBSTRING(`request_amount_recommended`,1,2),',',SUBSTRING(`request_amount_recommended`,3,3),',',SUBSTRING(`request_amount_recommended`,5,3))
END
,' | ',
ROUND(`Duration in Years`),' year(s)'
)
Re: Custom Date & Timestamp Formatting in Analyzer View of Cards
@MarkSnodgrass @Jason @trafalger I'm excited to share that @Jonathan53891's VERY helpful article above (totally voluntary) was just published in Domo's KB. This is the first of it's kind and we are very excited about it. Read the article HERE!
