Best Of
Re: Count that accomodates NULL
@PJG You will need to add IFNULL around every value in your beast mode to force nulls to zero like this: IFNULL(Value1,0) + IFNULL(Value2,0) + …
Re: How to filter column by a specific language
Hello @Mansa_TCC,
If the title in Portuguese or Spanish contains only standard English letters, this might be why you can't filter them.
This can be done with Domo Jupyter if you have access to this function.
Here is the script:
pip install langdetect
from langdetect import detect
#Function to detect language
def detect_language(title):
try:
return detect(title)
except LangDetectException:
return 'Unknown'
#Apply the function to create the 'Language' column
sample['Language'] = sample['title'].apply(detect_language)
#Display the DataFrame with the new column
sample
Here is the result:
You can then write to the dataset with the new column.
I can't think of any other solution. Unfortunately, I don't see 'langdetect' as part of the Python in ETL and you can't do it from there. If you need, I can help you with the rest of the steps.
Re: Logic - link original submit date to all subsequent back orders
Hello @Jbrorby ,
I had a similar problem to solve in the past. I was able to do it with a Python script in ETL:
Here is the result table:
Copy and past this in your ETL to find the solution and the Python script:
{"contentType":"domo/dataflow-actions","data":[{"icon":"python","name":"Python Script","category":"scripting","description":"Write a custom Python script to manipulate your data","helpUrl":"/articles/360045485833#1.","type":"PythonEngineAction","feature":"dataflows-scripting-python","config":{"curView":"configuration","hasSources":true,"hasTargets":true,"buttons":[{"id":"CODE_TEMPLATE","text":"Use code template","icon":"term"},{"id":"RUN_PREVIEW","text":"Run Preview","icon":"play-circle-outline"},{"id":"CANCEL_PREVIEW","text":"Cancel Preview","icon":"x-circle-outline"}]},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":true,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["8560b80c-e7e1-48b3-b20b-ecbd544bdd01"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}}],"previewData":{},"gui":{"x":204,"y":48,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"fillMissingWithNull":true,"additions":[{"name":"submitted at","dataType":"DATETIME"},{"name":"order id","dataType":"LONG"},{"name":"original order id","dataType":"LONG"},{"name":"first order","dataType":"LONG"}],"script":"# Import the domomagic package into the script\nfrom domomagic import *\nimport pandas as pd\nimport numpy as np\n\n# read data from inputs into a data frame\ninput1 = read_dataframe('Domo Forum Answer')\n\n# write your script here\n\nrelations = {}\n\nfor index, row in input1.iterrows():\n order = row['order id']\n replacement = row['original order id']\n if not pd.isna(replacement):\n relations[order] = replacement\n\n# Function to find the first order in a gives series of orders\ndef find_first_order(order):\n first_order = order\n while order in relations:\n first_order = order\n order = relations[order]\n return first_order\n\n \n# Create a new column 'first order' in the DataFrame\ninput1['first order'] = input1['order id'].apply(find_first_order)\n\n# write a data frame so it's available to the next action\nwrite_dataframe(input1)","condaEnv":null,"id":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","settings":{},"label":"Python Script"},{"icon":"join-left-outer","name":"Join Data","category":"combine-data","description":"Combine rows from two DataSets into one.","type":"MergeJoin","helpUrl":"/articles/360044876194#3.","config":{"curView":"configuration","maxTargets":2,"minTargets":2,"hasSources":true,"hasTargets":true},"dirtyPreview":false,"previewRunning":false,"validationRunning":false,"removeByDefault":false,"configured":true,"ready":true,"enabled":true,"selectedTabHasMessage":false,"dependsOn":["07446cf8-e2c6-4a26-ba22-a532e3d4493d","ed1a6969-2fb0-49e9-a286-afc0dce95311"],"children":["c7f25cef-2c99-4d7c-8009-d4c4e34fe816"],"outputSchema":[{"name":"submitted at","id":"submitted at","type":"DATETIME","typeLabel":"Timestamp","icon":"icon-calendar-simple","metadata":{}},{"name":"order id","id":"order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"original order id","id":"original order id","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"first order","id":"first order","type":"LONG","typeLabel":"Integer","icon":"icon-numbers","metadata":{}},{"name":"NEW_COLUMN","id":"NEW_COLUMN","type":"DATE","typeLabel":"Date","icon":"icon-calendar-simple","metadata":{}}],"previewData":{},"gui":{"x":312,"y":132,"color":null,"colorSource":null,"sampleJson":null},"notes":[],"step1":"07446cf8-e2c6-4a26-ba22-a532e3d4493d","step2":"ed1a6969-2fb0-49e9-a286-afc0dce95311","joinType":"LEFT OUTER","keys1":["first order"],"keys2":["order id"],"on":null,"schemaModification1":[],"schemaModification2":[{"name":"submitted at","rename":"NEW_COLUMN","remove":false},{"name":"order id","rename":"Domo Forum Answer.order id","remove":true},{"name":"original order id","rename":"Domo Forum Answer.original order id","remove":true}],"relationshipType":"MANY_TO_MANY","id":"8560b80c-e7e1-48b3-b20b-ecbd544bdd01","settings":{},"label":"Join Data"}]}
Re: Sub-Category of a Category?
Thanks for the info - After posting this question and working through the process, I got to the point where I thought of just about the same solution. Thank you for confirming to me that this was in fact the way!
damen
Re: Click to select, not deselect
You can do this with quick filters. Click Select All for the quick filter in analyzer view and then save the card. All data will still be shown and once one is selected, it will filter for that value.
Re: How to Join two Group By without matching pair column
You could add phase totals by adding another branch of the original input with its own Group By, grouping by only Phase and doing a Count Distinct on Project Id called Total Project Count, and then Join that branch back in to your final result using Phase on both sides as the join key.
rco
Re: Group By Tile to JOIN Tile Produces Unexpected Result. Using an Output Tile as an Input Fixes It
Previews operate on a limited number of rows from their inputs, by default just 10k rows:
This can cause Group By aggregates to look incomplete, Joins to miss when they should hit, and Filters to produce zero rows when they should produce some.
rco
Re: Video for Page Filters with Forms
I've passed this on the PM over Form Builder. :)
Rating Card
Anyone have any idea or work around to show ratings besides just table or bar chart? I have a survey where people rated items 1-5 stars. I would like to show that in a format like this based on responses.
Ashleigh
Re: How to Join two Group By without matching pair column
Unfortunately there's currently no way to do this in a way that will dynamically adapt to the actual set of unique Requestor Organizations. If you can tolerate enumerating the Requestor Organizations explicitly, then this solution works:
In the "upper" section, all we're doing is creating a cross-join table of all the possible combinations of Requestor Organization and Phase, with a fake Project Id of -1. We then append this fake data onto our real data. This ensures that in the Group By, where we group by Requestor Organization+Phase, we end up with a group for every combination, even those combinations that didn't actually appear in the input (e.g. Phase=Cancelled, Requestor Organization=DES).
Here's what the preview data should look like at the Project ID Const tile:
And, in case it's helpful, here is what the Cross Join configuration looks like:
(As I'm writing this, it occurs to me that we could save a tile by just using the same constant for both the cross join key and the dummy Project Id.)
The aggregation we use is Count Distinct on the Project ID column, and we call it Project Count. Then, to correct for our dummy -1 Project Id, we just subtract 1 from the Project Count using an Add Formula tile. Finally, we pivot.
Here is the configuration for the Group By:
And the Pivot:
The pivot is where we have to enumerate all the Requestor Organizations, as I mentioned. This is unfortunate because any changes to the set of Requestor Organizations will require modifications to the flow.
rco