Best Of

Re: Adjustable % column

Ahh, I see that now in the sample data I mocked up. When I changed the beast mode from

(`Lines on Time`) / (`Number of Lines`)

to

SUM(`Lines on Time`) / SUM(`Number of Lines`)

then the average aggregation works properly.

Give that a try and see how it works for you.

MarkSnodgrass

2

Re: Business days in Date_Add Interval

Hi @AJ2020

This was a fun question that I had to play around with. Currently you can't just say "add X business days" to the date_add function (though it'd be great). The following beast mode will add X number of business days to a date however it's not smart enough to exclude holidays.

Here's the basic version of it replace all instances of 6 with the number of business days :

`dt` + INTERVAL (FLOOR(6/5) * 7 + CASE WHEN MOD(6,5) + DAYOFWEEK(`dt`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`dt`)/7) ELSE 0 END + 1) DAY

dt is your date column.

FLOOR(6/5) *7

Is determining the number of full weeks for the number of business days

CASE WHEN MOD(6,5) + DAYOFWEEK(`dt`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`dt`)/7) ELSE 0 END

Is determining if the partial week would overlap Saturday (7) / the weekend and then subtract a day if we're starting on Saturday (1 day weekend and not 2 day weekend)

And finally we add 1 to correctly offset the addition of the days.

This makes it a bit more complex logically but simpler in trying to calculate the number of actual days from the business days. You don't have to pre-calculate the number of actual days based on your business dates.

To put a nice bow on your request:

CASE WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`), INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY) --New after 3 PM

WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(1/5) * 7 + CASE WHEN MOD(1,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --New before 3 PM

  WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(4/5) * 7 + CASE WHEN MOD(4,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --Renew after 3 PM

WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(3/5) * 7 + CASE WHEN MOD(3,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + 1) DAY)) --Renew before 3 PM

ELSE ' '

END

GrantSmith

2

Re: Combining Different Rows into a Single Row

Hi @user077529

You can utilize a Group By tile in a Magic ETL dataflow to group your rows together and then use the "Combine strings by ," option in the group by tile.

GrantSmith

1

Re: Trying to Split a Text Field with Time data (HH:MM:SS) into usable data

Hi @psmith

Regular expressions are one way to solve your problem. Assuming your text is ONLY HH:M:SS OR MM:SS you can utilize a replace text tile (Magic ETL 1.0 or 2.0) to pad your MM:SS formatted strings to be 00:MM:SS

Screen Shot 2021-02-19 at 3.19.51 PM.png

Here's the regular expression you can copy and past:

^(\d{2}):(\d{2})$

And here's the Replacement string:

00:$1:$2

Alternatively you can copy and paste this code into your Magic ETL dataflow and it should* populate the tile automatically for you. You'll just need to select the correct column storing your string and attach the input.

{"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"a75b422e-7cb5-4c7f-a166-932ed27bd321","type":"ReplaceString","gui":{"x":420,"y":168},"dependsOn":["d0858ff2-48dc-4f30-8151-ba3426ba302a"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"MMSS","useRegex":true,"replaceString":"^(\\d{2}):(\\d{2})$","replaceByString":"00:$1:$2","wholeWord":false,"caseSensitive":false}]}]}

GrantSmith

1

Re: Trying to Split a Text Field with Time data (HH:MM:SS) into usable data

@psmith it turned out to be not too difficult to create samples for you in both versions of Magic ETL. Here is what they look like

Version 1

In the 1st string operations tile, extract the seconds by choosing Right 2. Extract the minutes by choosing right 5. (You're initially grabbing more than the minutes, but we will take care of that in the next tile.)

In the 2nd string operations tile, extract the minutes by choosing Left 2.

In the Replace Text tile, choose the minutes field you just created, enter : (colon) for the term to search for and leave the 3rd box blank in what to replace it with.

In the Set Column Type tile, choose your seconds and minutes fields you created and set them to type of integer.

Version 2

Use the Add Formula Tile and use the following formulas:

Seconds: RIGHT(`Time`,2)

Minutes: REPLACE(LEFT(RIGHT(`Time`,5),2),':','')

Use the Alter Columns tile to change the data type to integer for each of those. Results should look like this based on my sample data:

MarkSnodgrass

2

Re: Anyone know how the dropdown filter card was made in the Domo Covid-19 public dashboard?

@hamza_123 yeah you can use a custom app to do anything!

Ryan O and his team are the folks that build custom apps in Domo, but I can show you how I did a similar thing.

In a nutshell, the pieces you'll need:

Domo Dev Studio prereqs (ryuu etc.)

https://developer.domo.com/docs/dev-studio/dev-studio-overview

Then:

SQL API to query the list of values from the source dataset (the contents of the dropdown menu)

https://developer.domo.com/docs/dev-studio-references/data-api#SQL%20API

You'll use a component like

https://getbootstrap.com/docs/4.0/components/dropdowns/

to render the dropdown menu and feed the contents in.

then when users make a selection, OnUpdate, you'll call a function that passes a "filter" message from your iFrame (the custom app) to window.parent and filters the dataset.

In the domoJS there's a method built-in, but you can obviously customize it if you're doing a multi-select.

https://developer.domo.com/docs/dev-studio-references/domo-js

domo.filterContainer = function (column, operator, values, dataType) {

var userAgent = window.navigator.userAgent.toLowerCase(),

safari = /safari/.test(userAgent),

ios = /iphone|ipod|ipad/.test(userAgent);

var message = JSON.stringify({

event: 'filter',

filter: {

columnName: column,

operator: operator,

values: values,

dataType: dataType,

},

});

if (ios && !safari) {

window.webkit.messageHandlers.domofilter.postMessage({

column: column,

operand: operator,

values: values,

dataType: dataType,

});

} else {

window.parent.postMessage(message, '*');

}

};

jaeW_at_Onyx

1

Re: How to get aggregated data without losing rows

@user094816 you can do this very easily in the ETL. See example in the image below.

Start with your dataset and then add a Group by tile and do a count of your column you want counted.

Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.

This will keep all your rows and show the total from the group by tile as well.

MarkSnodgrass

2

Re: conditionally start a dataflow

Hey @kjones140

I haven't tried this before but you might be able to utilize a recursive dataflow where you have an input dataset of the new data and also the output dataset as the input. You could then perform some auditing as a 3rd dataset on your new data and determine if it's good or not and add a column with value of 1 (success) or 0 (fail) (and a single row)

Your new dataset would have a constant added to it with an audit value of 1. Your existing output dataset would have a constant of 0. You then append the new data with the historical dataset and then join to the output of your audit path. This way if the audit passes (1) it would select the new data, if it fails (0) it'd select the old historical data.

Essentially you're conditionally selecting the dataset to use.

GrantSmith

1

Re: Extracting data from Domo

in addition to @GrantSmith 's answers you can take a look at the ODBC connector, https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Other_Methods/Domo_ODBC_Data_Driver, there are commercial implications to aquiring access to the driver,but it does create a more SQL friendly workflow.

PyDomo would probably be the easiest option. In this tutorial we're executing a dataflow, but it's relatively easy to switch the use case / functions used to extract data.

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

regardless of which extraction method you used, I recommend you build a dataset view as the datasource and then subset that datasource to the correct columns and apply appropriate filters. you don't really want to be passing large datasets over API if you only need to pass a subset of the data (a few columns, or new rows since yesterday.)

jaeW_at_Onyx

1

Re: Extracting data from Domo

Hi @Brandon

You can schedule a report to send an email with a card at a specific time where you can choose to include the data as an attachment if you're wanting to export data that's been processed / visualized.

If you're looking to export raw data you can utilize the Java CLI tool (https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool) - specifically the export-data or export-to-s3 commands.

Alternatively if you're more technical / programmatical you can interface with the public API endpoints with the PyDomo (Python - https://github.com/domoinc/domo-python-sdk) or the RDomo (R - https://github.com/domoinc/rdomo) packages to extract the data. You could then subsequently upload the data to Oracle within the same scripts to simplify your data pipeline.

GrantSmith

1