Best Of
Re: Isolate email addresses from a column using Magic ETL or BeastMode
Sorry @blittle - Appears it didn't get adequately submitted. I've updated the formula in the comment above.
Need clarification on functionality
I have a question on revision history:
Besides manually making changes, is there a way to roll back to a previous card version or at least view the card setup from an earlier version? If so, what are the steps?
Re: API string question
In the JSON No Code connector that Grant describes, you will see some configuration fields for parameters. It might be easier to understand looking there.
When parameters are passed to an API, they are either passed in line, like HTML does it on webpages.
www.mysite.com?param=1
Or they are passed as part of a header…behind the scene so to speak. So its not visible on the URL being called.
Re: Different time zone
@VictorLuiz Once you find out which time zone the instance is set to, you can change "GMT-8" in your formula to the corresponding IANA time zone name (e.g. "America/Los_Angeles").
Re: How do i fill in rows of data with previous row info?
Is there a way you can add a row number to your original dataset? We can in theory do this in ETL but without having a real column to sort on, I'm not sure if the method I've used that worked for a small dataset will keep on working all the time.
Original:
Backfilled (you could rename or remove columns as needed, but I left most there so that you'd know where this comes from, the way it has been structure will also allow it to be backfill should the year in Row 4 would be missing):
This is what the ETL Looks like:
And here's the code you could copy to the clipboard and paste to have it recreated on your end:
{"contentType":"domo/dataflow-actions","data":[{"name":"TEST - Backfill","id":"cf74d6f1-44ee-4af8-af86-3f4fa7bfc647","type":"LoadFromVault","gui":{"x":24,"y":348,"color":3238043,"colorSource":null,"sampleJson":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"fe80921c-8e3b-4f53-b605-54393dd9f1f3","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Add Constants","id":"4281ab41-abc7-42cb-a153-978b311f5748","type":"Constant","gui":{"x":120,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["cf74d6f1-44ee-4af8-af86-3f4fa7bfc647"],"removeByDefault":false,"notes":[],"fields":[{"name":"Helper","type":"LONG","expr":null,"value":"1"}]},{"name":"Rank & Window","id":"6c20902a-36b8-446f-a9ac-31d8b9361345","type":"WindowAction","gui":{"x":216,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["4281ab41-abc7-42cb-a153-978b311f5748"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"RowNumber","operation":{"type":"RANKING","operationType":"ROW_NUMBER"}}],"orderRules":[{"column":"Helper","caseSensitive":false,"ascending":true}],"groupRules":[]},{"name":"SelectYears","id":"f50f0e8e-6c1b-4201-b67d-a4ad504586fe","type":"SelectValues","gui":{"x":384,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Year","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"SelectMonths","id":"5a8bbefa-d8ad-406f-a963-d1a550106c5b","type":"SelectValues","gui":{"x":456,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345"],"removeByDefault":false,"notes":[],"fields":[{"name":"Month","rename":null},{"name":"RowNumber","rename":null},{"name":"Helper","rename":null}]},{"name":"YearValues","id":"0a1ba943-267d-475b-bae9-a4a476f91c1b","type":"Filter","gui":{"x":480,"y":432,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["f50f0e8e-6c1b-4201-b67d-a4ad504586fe"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Year","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"MonthValues","id":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","type":"Filter","gui":{"x":564,"y":252,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5a8bbefa-d8ad-406f-a963-d1a550106c5b"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"Month","rightExpr":null,"operator":"NN","andFilterList":[]}]},{"name":"Join Possible Years","id":"22d7db5d-c722-46ba-806b-c1901337de88","type":"MergeJoin","gui":{"x":588,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["6c20902a-36b8-446f-a9ac-31d8b9361345","0a1ba943-267d-475b-bae9-a4a476f91c1b"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"6c20902a-36b8-446f-a9ac-31d8b9361345","step2":"0a1ba943-267d-475b-bae9-a4a476f91c1b","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Year","rename":"YearValues.Year","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"YearValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Join Possible Months","id":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","type":"MergeJoin","gui":{"x":708,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["22d7db5d-c722-46ba-806b-c1901337de88","1661e24d-d73d-46c9-99e8-fd753dd2b1ba"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"22d7db5d-c722-46ba-806b-c1901337de88","step2":"1661e24d-d73d-46c9-99e8-fd753dd2b1ba","keys1":["Helper"],"keys2":["Helper"],"schemaModification1":[],"schemaModification2":[{"name":"Month","rename":"MonthValues.Month","remove":false},{"name":"Helper","rename":"","remove":true},{"name":"RowNumber","rename":"MonthValues.RowNumber","remove":false}],"partitioningInputId":""},{"name":"Get Latest Year and Moth Rows","id":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","type":"GroupBy","gui":{"x":816,"y":444,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"RowNumber"}],"partitionedAggregation":false,"fields":[{"name":"LatestYearRow","expression":"MAX(CASE WHEN RowNumber >= YearValues.RowNumberTHEN YearValues.RowNumberEND)","settings":null},{"name":"LatestMonthRow","expression":"MAX(CASE WHEN RowNumber>= MonthValues.RowNumberTHEN MonthValues.RowNumberEND)","settings":null}]},{"name":"Limit to only the latest combination","id":"5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec","type":"MergeJoin","gui":{"x":936,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["2fc9caa4-31db-4d32-bb26-cc008b702f1e","8c5877d9-ccfd-49fc-ae1c-1b23bcb96723"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"8c5877d9-ccfd-49fc-ae1c-1b23bcb96723","step2":"2fc9caa4-31db-4d32-bb26-cc008b702f1e","keys1":["RowNumber","YearValues.RowNumber","MonthValues.RowNumber"],"keys2":["RowNumber","LatestYearRow","LatestMonthRow"],"schemaModification1":[{"name":"YearValues.RowNumber","rename":"","remove":true},{"name":"MonthValues.RowNumber","rename":"","remove":true}],"schemaModification2":[{"name":"RowNumber","rename":"","remove":true}],"partitioningInputId":""},{"name":"TEST - Backfilled","id":"cf727414-7253-4f02-a708-1e61cc5d7a6f","type":"PublishToVault","gui":{"x":1062,"y":348,"color":null,"colorSource":null,"sampleJson":null},"dependsOn":["5e9d2ebb-27a1-46a5-8ed5-85ab15ca9bec"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"00141f72-88f2-41dd-a0d7-800e1096d8d8","type":"DataFlow","name":"TEST - Backfilled","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}
Let us know if this helps!
Re: PERIOD_DIFF
@rmbourne It looks like you are missing the % sign at the beginning of your second DATE_FORMAT function, so your second date is not being properly converted to a date
Unable to Create Same Time vs Yr Ago
I'm trying to create a dynamic Comparison Value that adjusts the view based on Time Period Selection. I was able to create a Gauge Value that works, but am struggle with the Beast Mode that would allow the end user to view vs same time year ago value.
I have the code below and have tried various adjustments, but keep getting a syntax error from Domo (which isn't helpful at all).
CASE
WHEN Time Period Selection: = '4' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '13' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 13 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '26' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 26 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = '52' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 52 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
WHEN Time Period Selection: = 'YTD' AND YEAR(WEEK_STARTING) = YEAR(CURRENT_DATE()) - 1 AND WEEK_STARTING BETWEEN DATE_TRUNC('YEAR', CURRENT_DATE() - INTERVAL 1 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
ELSE 0
END
MarshAm
Re: Dashboard Layouts - Control Padding Between Cards
@MarkSnodgrass This is currently on our roadmap and we are scoping ways that could allow users to adjust spacing on dashboards.
Khushboo
Re: Datasets not accessed in a while
I don't know the answer to this, but I agree this would be helpful to know. I would also like to know how many days does it take for a dataset to not be accessed before the dataset goes into this state.
Re: Joining and getting all dates between start and end date
Using magic ETL, take the date dimension dataset (Domo Dimensions connector) and feed it into a Add Constant tile to create a new field called "join column" with a value of 1. Do the same for your input dataset. Feed both of those into the join based on the join column. Then you feed that into a filter field with your BETWEEN clause. The joining on the join column will do a cartesian join and add all dates for every record then you're filtering out the rows where the dates from the dimension dataset aren't within your range.