Transforming Data before a Join
Hello,
I've been trying out DOMO for a week now, and it seems to work a bit different than other BI tools. What I want to do, is add a calculated column to a table, based on a formula (in this case, based on DATEDIFF since I want to calculate Age from Date of Birth). After this, I want to add a conditional column Age Group, that organizes the different Ages into their corresponding Age Groups.
Now I know I can accomplish this easily by a few beastmodes, but the problem is that (if I'm right) this only creates virtual columns to use directly in charts. I want to add the columns mentioned above, and then join another table based on the Age Group values.
I already looked at Magic ETL but it doesn't seem to be possible there. Would this be possible by using SQL? Or is this even possible at all within DOMO?
Best Answer
-
This is definitely something that can be handled in a MySQL dataflow.
Select
*,
floor(datediff(curdate(),`Date_of_Birth`)/365) as `Age`
from data_table
then you can use the `Age` field to create your `Age Bucket` Field:
select
*,
case when `Age`<18 then 'Minor'
when `Age`<30 then 'Young Adult'
when `Age`<50 then 'Middle Age'
... etc.
end as `Age Bucket`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
You can do a seudo Datediff in Magic ETL
First Convert the date columns into decimal.
Then subtract.
Then convert to Days (multiply by 1 440 000).
When Dates are converted to decimal 1 equals 1 miliseconds (and for reference 0 is 1 Jan 1970)
0 -
This is definitely something that can be handled in a MySQL dataflow.
Select
*,
floor(datediff(curdate(),`Date_of_Birth`)/365) as `Age`
from data_table
then you can use the `Age` field to create your `Age Bucket` Field:
select
*,
case when `Age`<18 then 'Minor'
when `Age`<30 then 'Young Adult'
when `Age`<50 then 'Middle Age'
... etc.
end as `Age Bucket`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive